﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.IO;
using System.Xml;
using System.Data.OleDb;
/**********************************
 *【类名】DBHub
 *【说明】大数据量导入
 *【作者】Naki
 *【日期】2016.5.24
 *【版本】1.0 Release
 *【目录】
 * 1.<数据导入>DataImport
 *    1.1.判断dt字段格式与table格式是否一致，返回不一致的位置和列名称
 *    1.2.判断是否有重复数据，参数控制“跳过重复数据”或“update重复数据”
 *    1.3.以bulk方式执行写入（对于新增数据项）
 * 2.<数据更新>DataUpdate
 *    1.1  1.2同上
 *    1.3.以bulk方式执行写入（对于新增数据项），以adapter方式执行更新和删除（对于已存在项）
 *【更细日志】
 * 1.简化了返回值，统一改成长度为2的字符数组，第一个元素是返回值，第二个元素是返回值的说明                     by  clementine  2016.6.19
 * 2.空值问题，在"dt copy to bulkDataTable"处，如果tempRow[j]字段为时间类型，不能直接赋值为string.Empty    by  clementine  2016.12.8
 * 3.遍历循环中的计数器计算错误，在"handle dtTableUpdate"处，遍历循环的时候如果有多个主键，跳过了第二个及以后的主键  by  clementine  2016.12.8
 * 4.解析tableName列名和数据类型的时候增加对schemaName的判断   by  clementine  2017.8.16
 * 5.修复了几处计数器使用错误，导致返回操作行数显示不正确  by clementine  2020.12.31
 *【错误代码】
 * 0    参数dt没有数据(0行)
 * 1    执行成功
 * 31   KeyColumnName参数中的主键列名在数据库目标表中不存在
 * 32   参数dt表结构与数据库表结构不一致，dt缺少字段[value] 
 * 33   参数dt表结构与数据库表结构不一致，dt多出字段[value] 
 * 90   来自vs debug 的try/cacth报错
 * 98   KeyColumnNameIsNotExistIndb或者dtColumnNameIsNotMatchTodb变量值异常
 * 99   不存在的错误代码，看到这个错误代码等于见到鬼
 **********************************/
namespace DBHub
{
    public class DBHub
    {
        #region parameters

        const int bulkCopyTimeoutValue = 5000;  //bulkCopyTimeout

        #endregion

        /// <summary>
        /// 数据导入
        /// </summary>
        /// <param name="conn">连接对象</param>
        /// <param name="dt">数据集</param>
        /// <param name="schemaName">架构名</param>
        /// <param name="tableName">表名</param>
        /// <param name="DataKeyName">关键字名数组</param>（判断数据项的唯一性用）
        /// <param name="dup">对重复数据的处理方式</param>
        /// <returns>长度为2的字符数组<执行代码，提示><return value,message></returns>
        public string[] DataImport(SqlConnection conn, DataTable dt, string schemaName, string tableName, string sqlfilter, string[] KeyColumnName, DuplicateProcessMode dupType)
        {
            /**********************************
             * 流程：
             * 1.解析dt的字段，组装成一个数组
             * 2.查询表tableName的数据集ds
             * 3.判断dt中有哪些项在ds中已经存在
             **********************************/
            #region declare

            DataTable dtTableColumnName = new DataTable();       //tableName column name
            string TableName = tableName.Trim();                 //tableName
            string FullTableName = schemaName.Trim() + "." + tableName.Trim();  //schemaName.tableName
            DataTable dtTableOriginal = new DataTable();         //data table original in DB

            #endregion

            //事务开始
            if (conn.State != ConnectionState.Open)
            {
                conn.Open(); //open conn
            }

            SqlTransaction sqltrans = conn.BeginTransaction();   //Transaction Object

            try
            {
                using (conn)
                {
                    if (dt.Rows.Count == 0)
                    {
                        string[] iRel = { "[0]", "参数dt没有数据(0行)" };
                        return iRel;
                    }
                    else  //if dt has data
                    {
                        #region 解析tableName列名和数据类型

                        StringBuilder sbTableColumnName = new StringBuilder();
                        sbTableColumnName.Append("SELECT COL.NAME AS 列名,TYP.NAME AS 数据类型,COL.MAX_LENGTH AS 占用字节数,");
                        sbTableColumnName.Append("COL.PRECISION AS 数字长度,COL.SCALE AS 小数位数,COL.IS_NULLABLE AS 是否允许非空,COL.IS_IDENTITY AS 是否自增,");
                        sbTableColumnName.Append("CASE WHEN EXISTS ");
                        sbTableColumnName.Append("( SELECT 1 FROM SYS.INDEXES IDX ");
                        sbTableColumnName.Append("JOIN SYS.INDEX_COLUMNS IDXCOL ON (IDX.OBJECT_ID = IDXCOL.OBJECT_ID) ");
                        sbTableColumnName.Append("WHERE IDX.OBJECT_ID = COL.OBJECT_ID ");
                        sbTableColumnName.Append("AND IDXCOL.INDEX_COLUMN_ID = COL.COLUMN_ID AND IDX.IS_PRIMARY_KEY = 1) ");
                        sbTableColumnName.Append("THEN 1 ELSE 0 END AS 是否是主键,ISNULL(PROP.[VALUE],'-') AS 说明 ");
                        sbTableColumnName.Append("FROM SYS.COLUMNS COL LEFT JOIN SYS.TYPES TYP ");
                        sbTableColumnName.Append("ON (COL.SYSTEM_TYPE_ID = TYP.SYSTEM_TYPE_ID) ");
                        sbTableColumnName.Append("LEFT JOIN SYS.EXTENDED_PROPERTIES PROP ");
                        sbTableColumnName.Append("ON (COL.OBJECT_ID = PROP.MAJOR_ID AND PROP.MINOR_ID = COL.COLUMN_ID) ");
                        sbTableColumnName.Append("WHERE COL.OBJECT_ID =(select OBJECT_ID from sys.tables t , sys.schemas s where t.schema_id = s.schema_id ");
                        sbTableColumnName.Append("and s.name = @schemaName and t.name = @tableName) ");
                        sbTableColumnName.Append("ORDER BY COL.COLUMN_ID");

                        SqlDataAdapter adapterTableColumnName = new SqlDataAdapter
                        {
                            SelectCommand = new SqlCommand(sbTableColumnName.ToString(), conn, sqltrans)
                        };
                        adapterTableColumnName.SelectCommand.Parameters.Add(new SqlParameter("@schemaName", SqlDbType.NVarChar, 220));
                        adapterTableColumnName.SelectCommand.Parameters["@schemaName"].Value = schemaName;
                        adapterTableColumnName.SelectCommand.Parameters.Add(new SqlParameter("@tableName", SqlDbType.NVarChar, 220));
                        adapterTableColumnName.SelectCommand.Parameters["@tableName"].Value = TableName;
                        //explain in msdn about here
                        //https://msdn.microsoft.com/zh-cn/library/system.data.missingschemaaction(v=vs.90).aspx
                        // MissingSchemaAction adds any missing schema to the DataTable, including identity columns
                        adapterTableColumnName.MissingSchemaAction = MissingSchemaAction.AddWithKey;

                        // Fill the DataTable.
                        adapterTableColumnName.Fill(dtTableColumnName);

                        #endregion

                        #region fix sysname by clementine 2016.6.19

                        for (int i = 0; i < dtTableColumnName.Rows.Count; i++)
                        {
                            if (dtTableColumnName.Rows[i]["数据类型"].ToString() == "sysname")
                            {
                                dtTableColumnName.Rows[i].Delete();
                            }
                        }

                        dtTableColumnName.AcceptChanges();

                        #endregion

                        #region check two things

                        ArrayList ColumnNameInDB = new ArrayList();    //put columnname in ArrayList
                        foreach (DataRow cRow in dtTableColumnName.Rows)
                        {
                            ColumnNameInDB.Add(cRow["列名"].ToString());
                        }

                        //one:if KeyColumnName in db's column
                        int KeyColumnNameIsNotExistIndb = -1;  //column index of key not exist in dtTableColumnName

                        for (int i = 0; i < KeyColumnName.Length; i++)
                        {
                            if (ColumnNameInDB.Contains(KeyColumnName[i].ToString()))
                            {
                                //do nothing
                            }
                            else
                            {
                                KeyColumnNameIsNotExistIndb = i;
                                break;
                            }
                        }

                        //two:if dtColumnName match to db's column
                        int dtColumnNameIsNotMatchTodb = 0; //-1:dt missing column  -2:dt redundant column
                        int IndexOfdtMissingColumn = -1;    //dt missing column index
                        int IndexOfdtRedundantColumn = -1;  //dt Redundant column index

                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            if (ColumnNameInDB.Contains(dt.Columns[i].ColumnName))
                            {
                                //do nothing
                            }
                            else
                            {
                                dtColumnNameIsNotMatchTodb = -2;
                                IndexOfdtRedundantColumn = i;
                                break;
                            }
                        }

                        if (dtColumnNameIsNotMatchTodb == 0)
                        {
                            ArrayList ColumnNameInDT = new ArrayList();    //put columnname in ArrayList
                            foreach (DataColumn cColumn in dt.Columns)
                            {
                                ColumnNameInDT.Add(cColumn.ColumnName);
                            }
                            for (int j = 0; j < ColumnNameInDB.Count; j++)
                            {
                                if (ColumnNameInDT.Contains(ColumnNameInDB[j].ToString()))
                                {
                                    //do nothing
                                }
                                else
                                {
                                    dtColumnNameIsNotMatchTodb = -1;
                                    IndexOfdtMissingColumn = j;
                                    break;
                                }
                            }
                        }

                        #endregion

                        if (dtColumnNameIsNotMatchTodb == 0 && KeyColumnNameIsNotExistIndb == -1)
                        {
                            #region 构造执行bulk写入用datatable

                            int dtTableColumnCount = dtTableColumnName.Rows.Count;              //dtTableColumnName column count
                            DataTable bulkDataTable = new DataTable();                          //import datatable
                            DataColumn[] bulkDataColumn = new DataColumn[dtTableColumnCount];   //import datatable's DataColumn
                            System.Type columnType = typeof(System.String);                     //default column data type                 

                            for (int i = 0; i < dtTableColumnCount; i++)
                            {
                                switch (dtTableColumnName.Rows[i]["数据类型"].ToString())
                                {
                                    default:
                                        columnType = typeof(System.String);
                                        break;
                                    case "int":
                                        columnType = typeof(System.Int32);
                                        break;
                                    case "text":
                                        columnType = typeof(System.String);
                                        break;
                                    case "bigint":
                                        columnType = typeof(System.Int64);
                                        break;
                                    case "binary":
                                        columnType = typeof(System.Byte[]);
                                        break;
                                    case "bit":
                                        columnType = typeof(System.Boolean);
                                        break;
                                    case "char":
                                        columnType = typeof(System.String);
                                        break;
                                    case "datetime":
                                        columnType = typeof(System.DateTime);
                                        break;
                                    case "decimal":
                                        columnType = typeof(System.Decimal);
                                        break;
                                    case "float":
                                        columnType = typeof(System.Double);
                                        break;
                                    case "image":
                                        columnType = typeof(System.Byte[]);
                                        break;
                                    case "money":
                                        columnType = typeof(System.Decimal);
                                        break;
                                    case "nchar":
                                        columnType = typeof(System.String);
                                        break;
                                    case "ntext":
                                        columnType = typeof(System.String);
                                        break;
                                    case "numeric":
                                        columnType = typeof(System.Decimal);
                                        break;
                                    case "nvarchar":
                                        columnType = typeof(System.String);
                                        break;
                                    case "real":
                                        columnType = typeof(System.Single);
                                        break;
                                    case "smalldatetime":
                                        columnType = typeof(System.DateTime);
                                        break;
                                    case "smallint":
                                        columnType = typeof(System.Int16);
                                        break;
                                    case "smallmoney":
                                        columnType = typeof(System.Decimal);
                                        break;
                                    case "timestamp":
                                        columnType = typeof(System.DateTime);
                                        break;
                                    case "tinyint":
                                        columnType = typeof(System.Byte);
                                        break;
                                    case "uniqueidentifier":
                                        columnType = typeof(System.Guid);
                                        break;
                                    case "varbinary":
                                        columnType = typeof(System.Byte[]);
                                        break;
                                    case "varchar":
                                        columnType = typeof(System.String);
                                        break;
                                    case "Variant":
                                        columnType = typeof(System.Object);
                                        break;

                                }

                                bulkDataColumn[i] = new DataColumn(dtTableColumnName.Rows[i]["列名"].ToString(), columnType);
                                bulkDataTable.Columns.Add(bulkDataColumn[i]);
                            }

                            #endregion

                            #region dt copy to bulkDataTable

                            for (int i = 0; i < dt.Rows.Count; i++)
                            {
                                DataRow tempRow = bulkDataTable.NewRow();

                                for (int j = 0; j < dtTableColumnCount; j++)
                                {
                                    if (dt.Rows[i][j].ToString() == "" && dt.Columns[j].ColumnName != "位号")
                                    { tempRow[j] = DBNull.Value; }
                                    else
                                    { tempRow[j] = dt.Rows[i][j]; }
                                }

                                bulkDataTable.Rows.Add(tempRow);
                            }

                            #endregion

                            #region get original datatable

                            StringBuilder sbTableOriginal = new StringBuilder();
                            sbTableOriginal.Append("SELECT ");
                            sbTableOriginal.Append(bulkDataColumn[0].ColumnName);
                            for (int j = 1; j < bulkDataColumn.Length; j++)
                            {
                                sbTableOriginal.Append("," + bulkDataColumn[j].ColumnName);
                            }
                            sbTableOriginal.Append(" FROM ");
                            sbTableOriginal.Append(FullTableName);

                            if (sqlfilter != string.Empty)
                            {
                                sbTableOriginal.Append(" ");
                                sbTableOriginal.Append(sqlfilter);
                            }

                            SqlDataAdapter adapterTableOriginal = new SqlDataAdapter
                            {
                                SelectCommand = new SqlCommand(sbTableOriginal.ToString(), conn, sqltrans),
                                //adapterTableOriginal.SelectCommand.Parameters.Add(new SqlParameter("@tableName", SqlDbType.NVarChar, 220));
                                //adapterTableOriginal.SelectCommand.Parameters["@tableName"].Value = FullTableName;

                                //explain in msdn about here
                                //https://msdn.microsoft.com/zh-cn/library/system.data.missingschemaaction(v=vs.90).aspx
                                // MissingSchemaAction adds any missing schema to the DataTable, including identity columns
                                MissingSchemaAction = MissingSchemaAction.AddWithKey
                            };

                            // Fill the DataTable.
                            adapterTableOriginal.Fill(dtTableOriginal);

                            #endregion

                            #region judgement if data existed and handle it by DuplicateType

                            if (dupType == DuplicateProcessMode.Ignore)
                            {
                                #region put original datarow into ArrayList
                                //put original datarow into ArrayList

                                ArrayList ht = new ArrayList();
                                string hashkey = string.Empty;

                                foreach (DataRow myRow in dtTableOriginal.Rows)
                                {
                                    hashkey = string.Empty;

                                    foreach (string keyName in KeyColumnName)
                                    {
                                        hashkey += myRow[keyName].ToString() + ",";
                                    }

                                    //get rid of the last char ","
                                    hashkey = hashkey.Substring(0, hashkey.Length - 1);

                                    ht.Add(hashkey);
                                }

                                #endregion

                                #region ergodic bulkDataTable and modify

                                string XkeyValue = string.Empty;
                                DataTable bulkDataTableCache = bulkDataTable.Clone();   //can't remove row when bulkDataTable foreach is running
                                int rowIgnoreCount = 0;

                                foreach (DataRow myRow in bulkDataTable.Rows)
                                {
                                    XkeyValue = string.Empty;
                                    foreach (string keyName in KeyColumnName)
                                    {
                                        XkeyValue += myRow[keyName].ToString() + ",";
                                    }

                                    //get rid of the last char ","
                                    XkeyValue = XkeyValue.Substring(0, XkeyValue.Length - 1);

                                    if (ht.Contains(XkeyValue))  //if exist
                                    {
                                        rowIgnoreCount++;
                                    }
                                    else
                                    {
                                        bulkDataTableCache.Rows.Add(myRow.ItemArray);
                                    }
                                }

                                #endregion

                                #region bulk and commit

                                int dataInsertRowsCount = 0;

                                if (bulkDataTableCache != null)
                                {
                                    SqlBulkCopy sbc = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, sqltrans)
                                    {
                                        BulkCopyTimeout = bulkCopyTimeoutValue,
                                        NotifyAfter = bulkDataTableCache.Rows.Count,
                                        DestinationTableName = FullTableName
                                    };
                                    sbc.WriteToServer(bulkDataTableCache);
                                    dataInsertRowsCount = bulkDataTableCache.Rows.Count;
                                }
                                else
                                {
                                    dataInsertRowsCount = 0;
                                }


                                sqltrans.Commit();  //commit is here

                                string[] iRel = { "[1]", "执行成功，" + "插入:" + dataInsertRowsCount.ToString() + "，忽略:" + rowIgnoreCount.ToString() };
                                return iRel;

                                #endregion

                            }
                            else if (dupType == DuplicateProcessMode.Update)  //if update
                            {
                                #region put original datarow into ArrayList
                                //put original datarow into ArrayList

                                ArrayList ht = new ArrayList();
                                string hashkey = string.Empty;
                                DataTable dtTableUpdate = dtTableOriginal.Clone();   //use for update   

                                foreach (DataRow myRow in dtTableOriginal.Rows)
                                {
                                    hashkey = string.Empty;

                                    foreach (string keyName in KeyColumnName)
                                    {
                                        hashkey += myRow[keyName].ToString() + ",";
                                    }

                                    //get rid of the last char ","
                                    hashkey = hashkey.Substring(0, hashkey.Length - 1);

                                    ht.Add(hashkey);
                                }

                                #endregion

                                #region ergodic bulkDataTable and modify

                                string XkeyValue = string.Empty;
                                DataTable bulkDataTableCache = bulkDataTable.Clone();   //can't remove row when bulkDataTable foreach is running
                                                                                        //关于【集合已修改；可能无法执行枚举操作。】的问题解决,留存待退休以后研究
                                                                                        //http://www.cnblogs.com/luckly-hf/archive/2012/08/08/2628025.html

                                foreach (DataRow myRow in bulkDataTable.Rows)
                                {
                                    XkeyValue = string.Empty;
                                    foreach (string keyName in KeyColumnName)
                                    {
                                        XkeyValue += myRow[keyName].ToString() + ",";
                                    }

                                    //get rid of the last char ","
                                    XkeyValue = XkeyValue.Substring(0, XkeyValue.Length - 1);

                                    if (ht.Contains(XkeyValue))  //if exist
                                    {
                                        dtTableUpdate.Rows.Add(myRow.ItemArray);    //add this row to update table
                                                                                    //bulkDataTable.Rows.Remove(myRow);         //I want to remove this row from bulk table,but can't remove row when bulkDataTable foreach is running
                                    }
                                    else
                                    {
                                        //add this row into bulkDataTableCache
                                        bulkDataTableCache.Rows.Add(myRow.ItemArray);
                                    }
                                }

                                #endregion

                                #region handle dtTableUpdate

                                DataTable group = new DataTable();   //original dt
                                SqlDataAdapter adapterUpdate = new SqlDataAdapter
                                {
                                    SelectCommand = new SqlCommand(sbTableOriginal.ToString(), conn, sqltrans)
                                };
                                //adapterUpdate.SelectCommand.Parameters.Add(new SqlParameter("@tableName", SqlDbType.UniqueIdentifier));
                                //adapterUpdate.SelectCommand.Parameters["@tableName"].Value = FullTableName;

                                //把key column从bulkDataColumn里拿出来
                                List<DataColumn> listValueColumn = bulkDataColumn.ToList();
                                List<DataColumn> listKeyColumn = new List<DataColumn>();     //存放Key

                                foreach (string keyName in KeyColumnName)
                                {
                                    for (int i = 0; i < listValueColumn.Count; i++)
                                    {
                                        if (keyName == listValueColumn[i].ColumnName)
                                        {
                                            listKeyColumn.Add(listValueColumn[i]);
                                            listValueColumn.RemoveAt(i);
                                            break;
                                        }
                                    }
                                }

                                //【BUG】  重大bug  clementine  2016.12.5
                                //foreach (string keyName in KeyColumnName)
                                //{
                                //    for (int i = 0; i < bulkDataColumn.Length; i++)
                                //    {
                                //        if (keyName == bulkDataColumn[i].ColumnName)
                                //        {
                                //            listKeyColumn.Add(bulkDataColumn[i]);
                                //            listValueColumn.RemoveAt(i);
                                //            break;
                                //        }
                                //    }
                                //}

                                DataColumn[] newlistValueColumn = listValueColumn.ToArray();
                                DataColumn[] newlistKeyColumn = listKeyColumn.ToArray();

                                //组装sbTableUpdate语句
                                StringBuilder sbTableUpdate = new StringBuilder();
                                sbTableUpdate.Append("UPDATE ");
                                sbTableUpdate.Append(FullTableName);
                                sbTableUpdate.Append(" SET ");
                                sbTableUpdate.Append(newlistValueColumn[0].ColumnName);
                                sbTableUpdate.Append("=@t0");
                                for (int j = 1; j < newlistValueColumn.Length; j++)
                                {
                                    sbTableUpdate.Append("," + newlistValueColumn[j].ColumnName);
                                    sbTableUpdate.Append("=@t");
                                    sbTableUpdate.Append(j.ToString());
                                }
                                sbTableUpdate.Append(" WHERE ");
                                sbTableUpdate.Append(newlistKeyColumn[0].ColumnName);
                                sbTableUpdate.Append("=@c0");
                                for (int k = 1; k < newlistKeyColumn.Length; k++)
                                {
                                    sbTableUpdate.Append(" AND ");
                                    sbTableUpdate.Append(newlistKeyColumn[k].ColumnName);
                                    sbTableUpdate.Append("=@c");
                                    sbTableUpdate.Append(k.ToString());
                                }

                                adapterUpdate.UpdateCommand = new SqlCommand(sbTableUpdate.ToString(), conn, sqltrans);
                                //Add the parameter for the Update value. 
                                for (int p = 0; p < newlistValueColumn.Length; p++)
                                {
                                    SqlDbType sdtp = GetDBType(newlistValueColumn[p].DataType);
                                    adapterUpdate.UpdateCommand.Parameters.Add(new SqlParameter("@t" + p.ToString(), sdtp, newlistValueColumn[p].MaxLength, newlistValueColumn[p].ColumnName));
                                }

                                //Add the parameter for the Condition value. 
                                for (int c = 0; c < newlistKeyColumn.Length; c++)
                                {
                                    SqlDbType sdtc = GetDBType(newlistKeyColumn[c].DataType);
                                    adapterUpdate.UpdateCommand.Parameters.Add(new SqlParameter("@c" + c.ToString(), sdtc, newlistKeyColumn[c].MaxLength, newlistKeyColumn[c].ColumnName));
                                }
                                adapterUpdate.UpdateCommand.UpdatedRowSource = UpdateRowSource.Both;

                                // MissingSchemaAction adds any missing schema to 
                                // the DataTable, including identity columns
                                adapterUpdate.MissingSchemaAction = MissingSchemaAction.AddWithKey;

                                // Fill the DataTable.
                                adapterUpdate.Fill(group);

                                #endregion

                                #region handle group（update group）

                                int IsKeyColumnValueCompared = -1;   //default: -1
                                int IsValueColumnChanged = -1;       //default: -1

                                for (int i = 0; i < group.Rows.Count; i++)
                                {
                                    for (int j = 0; j < dtTableUpdate.Rows.Count; j++)
                                    {
                                        /********************************************
                                         * keyColumnName
                                         ********************************************/
                                        IsKeyColumnValueCompared = 1;       //1:yes , 2:no
                                        for (int n = 0; n < KeyColumnName.Length; n++)
                                        {
                                            if (group.Rows[i][KeyColumnName[n].ToString()].ToString() == dtTableUpdate.Rows[j][KeyColumnName[n].ToString()].ToString())
                                            { }
                                            else
                                            {
                                                IsKeyColumnValueCompared = 0;
                                                break;
                                            }
                                        }

                                        if (IsKeyColumnValueCompared == 1)
                                        {
                                            IsValueColumnChanged = 1;       //1:yes , 2:no
                                                                            //update group value
                                            for (int k = 0; k < newlistValueColumn.Length; k++)
                                            {
                                                if (group.Rows[i][newlistValueColumn[k].ColumnName].ToString() == dtTableUpdate.Rows[j][newlistValueColumn[k].ColumnName].ToString())
                                                { }
                                                else
                                                {
                                                    IsValueColumnChanged = 0;  //group.Rows[i]["X"] is not equal dtTableUpdate.Rows[i]["X"] 
                                                    break;
                                                }
                                            }

                                            if (IsValueColumnChanged == 0)
                                            {
                                                for (int k = 0; k < newlistValueColumn.Length; k++)
                                                {
                                                    group.Rows[i][newlistValueColumn[k].ColumnName] = dtTableUpdate.Rows[j][newlistValueColumn[k].ColumnName];
                                                }
                                            }
                                        }
                                        else
                                        { }
                                    }

                                }

                                DataTable dataChanges = group.GetChanges();
                                int dataChangesRowsCount = 0;
                                int dataInsertRowsCount = 0;

                                // Add the event handler.
                                //adapter.RowUpdated += new SqlRowUpdatedEventHandler(OnRowUpdated);

                                if (dataChanges != null)   //这里要判断一下，更新的行是否为空
                                {
                                    adapterUpdate.Update(dataChanges);   //adapter.Update()参数dataChanges不能为空 
                                    dataChangesRowsCount = dataChanges.Rows.Count;
                                }
                                else
                                {
                                    dataChangesRowsCount = 0;
                                }

                                #endregion

                                #region bulk and commit

                                if (bulkDataTableCache != null)
                                {
                                    SqlBulkCopy sbc = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, sqltrans)
                                    {
                                        BulkCopyTimeout = bulkCopyTimeoutValue,
                                        NotifyAfter = bulkDataTableCache.Rows.Count,
                                        DestinationTableName = FullTableName
                                    };
                                    sbc.WriteToServer(bulkDataTableCache);
                                    dataInsertRowsCount = bulkDataTableCache.Rows.Count;
                                }
                                else
                                {
                                    dataInsertRowsCount = 0;
                                }

                                string[] iRel = { "[1]", "执行成功，" + "插入:" + dataInsertRowsCount.ToString() + "，更新:" + dataChangesRowsCount.ToString() };
                                sqltrans.Commit();  //commit in the end
                                return iRel;

                                #endregion
                            }
                            else
                            {
                                //if come here you must see ghost
                                string[] iRel = { "[99]", "如果你见到这句话，那肯定是见鬼了" };
                                return iRel;
                            }

                            #endregion

                        }
                        else if (KeyColumnNameIsNotExistIndb != -1)
                        {
                            string msg = "KeyColumnName的列:[" + KeyColumnName[KeyColumnNameIsNotExistIndb].ToString() + "]在数据库表[" + schemaName + "].[" + tableName + "]中不存在";
                            string[] iRel = { "[31]", msg };
                            return iRel;
                        }
                        else if (dtColumnNameIsNotMatchTodb == -1)  //dt column missing
                        {
                            string msg = "dt缺少列:[" + ColumnNameInDB[IndexOfdtMissingColumn].ToString() + "]";
                            string[] iRel = { "[32]", msg };
                            return iRel;
                        }
                        else if (dtColumnNameIsNotMatchTodb == -2)  //dt column Redundant
                        {
                            string msg = "dt有多余的列:[" + dt.Columns[IndexOfdtRedundantColumn].ColumnName + "]";
                            string[] iRel = { "[33]", msg };
                            return iRel;
                        }
                        else
                        {
                            //if come here you must see ghost
                            string[] iRel = { "[98]", "KeyColumnNameIsNotExistIndb或者dtColumnNameIsNotMatchTodb变量值异常" };
                            return iRel;
                        }

                    }

                }
            }
            catch (Exception ex)
            {
                if (sqltrans != null && sqltrans.Connection != null)
                {
                    sqltrans.Rollback();
                }
                string[] iRel = { "[90]", ex.Message };
                return iRel;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close(); //关闭连接
                }
            }
        }

        /// <summary>
        /// 数据更新
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="dt"></param>
        /// <param name="schemaName"></param>
        /// <param name="tableName"></param>
        /// <param name="sqlfilter"></param>
        /// <param name="KeyColumnName"></param>
        /// <param name="dupMode">已经存在项处理方式</param>
        /// <param name="delMode">删除项处理方式</param>
        /// <returns>长度为2的字符数组<执行代码，提示><return value,message></returns>
        public string[] DataUpdate(SqlConnection conn, DataTable dt, string schemaName, string tableName, string sqlfilter, string[] KeyColumnName, DuplicateProcessMode dupMode)
        {
            /**********************************
             * 流程：
             * 1.解析dt的字段，组装成一个数组
             * 2.查询表tableName的数据集ds
             * 3.判断dt中有哪些项在ds中已经存在
             **********************************/
            #region declare

            DataTable dtTableColumnName = new DataTable();       //tableName column name
            string TableName = tableName.Trim();                 //tableName
            string FullTableName = schemaName.Trim() + "." + tableName.Trim();  //schemaName.tableName
            DataTable dtTableOriginal = new DataTable();         //data table original in DB

            #endregion

            //事务开始
            if (conn.State != ConnectionState.Open)
            {
                conn.Open(); //open conn
            }

            SqlTransaction sqltrans = conn.BeginTransaction();   //Transaction Object

            //try
            //{
            using (conn)
            {
                if (dt.Rows.Count == 0)
                {
                    string[] iRel = { "[0]", "参数dt没有数据(0行)" };
                    return iRel;
                }
                else  //if dt has data
                {
                    #region 解析tableName列名和数据类型

                    StringBuilder sbTableColumnName = new StringBuilder();
                    sbTableColumnName.Append("SELECT COL.NAME AS 列名,TYP.NAME AS 数据类型,COL.MAX_LENGTH AS 占用字节数,");
                    sbTableColumnName.Append("COL.PRECISION AS 数字长度,COL.SCALE AS 小数位数,COL.IS_NULLABLE AS 是否允许非空,COL.IS_IDENTITY AS 是否自增,");
                    sbTableColumnName.Append("CASE WHEN EXISTS ");
                    sbTableColumnName.Append("( SELECT 1 FROM SYS.INDEXES IDX ");
                    sbTableColumnName.Append("JOIN SYS.INDEX_COLUMNS IDXCOL ON (IDX.OBJECT_ID = IDXCOL.OBJECT_ID) ");
                    sbTableColumnName.Append("WHERE IDX.OBJECT_ID = COL.OBJECT_ID ");
                    sbTableColumnName.Append("AND IDXCOL.INDEX_COLUMN_ID = COL.COLUMN_ID AND IDX.IS_PRIMARY_KEY = 1) ");
                    sbTableColumnName.Append("THEN 1 ELSE 0 END AS 是否是主键,ISNULL(PROP.[VALUE],'-') AS 说明 ");
                    sbTableColumnName.Append("FROM SYS.COLUMNS COL LEFT JOIN SYS.TYPES TYP ");
                    sbTableColumnName.Append("ON (COL.SYSTEM_TYPE_ID = TYP.SYSTEM_TYPE_ID) ");
                    sbTableColumnName.Append("LEFT JOIN SYS.EXTENDED_PROPERTIES PROP ");
                    sbTableColumnName.Append("ON (COL.OBJECT_ID = PROP.MAJOR_ID AND PROP.MINOR_ID = COL.COLUMN_ID) ");
                    sbTableColumnName.Append("WHERE COL.OBJECT_ID =(select OBJECT_ID from sys.tables t , sys.schemas s where t.schema_id = s.schema_id ");
                    sbTableColumnName.Append("and s.name = @schemaName and t.name = @tableName) ");
                    sbTableColumnName.Append("ORDER BY COL.COLUMN_ID");

                    SqlDataAdapter adapterTableColumnName = new SqlDataAdapter
                    {
                        SelectCommand = new SqlCommand(sbTableColumnName.ToString(), conn, sqltrans)
                    };
                    adapterTableColumnName.SelectCommand.Parameters.Add(new SqlParameter("@schemaName", SqlDbType.NVarChar, 220));
                    adapterTableColumnName.SelectCommand.Parameters["@schemaName"].Value = schemaName;
                    adapterTableColumnName.SelectCommand.Parameters.Add(new SqlParameter("@tableName", SqlDbType.NVarChar, 220));
                    adapterTableColumnName.SelectCommand.Parameters["@tableName"].Value = TableName;

                    //explain in msdn about here
                    //https://msdn.microsoft.com/zh-cn/library/system.data.missingschemaaction(v=vs.90).aspx
                    // MissingSchemaAction adds any missing schema to the DataTable, including identity columns
                    adapterTableColumnName.MissingSchemaAction = MissingSchemaAction.AddWithKey;

                    // Fill the DataTable.
                    adapterTableColumnName.Fill(dtTableColumnName);

                    #endregion

                    #region fix sysname

                    for (int i = 0; i < dtTableColumnName.Rows.Count; i++)
                    {
                        if (dtTableColumnName.Rows[i]["数据类型"].ToString() == "sysname")
                        {
                            dtTableColumnName.Rows[i].Delete();
                        }
                    }

                    dtTableColumnName.AcceptChanges();

                    #endregion

                    #region check two things

                    ArrayList ColumnNameInDB = new ArrayList();    //put columnname in ArrayList
                    foreach (DataRow cRow in dtTableColumnName.Rows)
                    {
                        ColumnNameInDB.Add(cRow["列名"].ToString());
                    }

                    //one:if KeyColumnName in db's column
                    int KeyColumnNameIsNotExistIndb = -1;  //column index of key not exist in dtTableColumnName

                    for (int i = 0; i < KeyColumnName.Length; i++)
                    {
                        if (ColumnNameInDB.Contains(KeyColumnName[i].ToString()))
                        {
                            //do nothing
                        }
                        else
                        {
                            KeyColumnNameIsNotExistIndb = i;
                            break;
                        }
                    }

                    //two:if dtColumnName match to db's column
                    int dtColumnNameIsNotMatchTodb = 0; //-1:dt missing column  -2:dt redundant column
                    int IndexOfdtMissingColumn = -1;    //dt missing column index
                    int IndexOfdtRedundantColumn = -1;  //dt Redundant column index

                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        if (ColumnNameInDB.Contains(dt.Columns[i].ColumnName))
                        {
                            //do nothing
                        }
                        else
                        {
                            dtColumnNameIsNotMatchTodb = -2;
                            IndexOfdtRedundantColumn = i;
                            break;
                        }
                    }

                    if (dtColumnNameIsNotMatchTodb == 0)
                    {
                        ArrayList ColumnNameInDT = new ArrayList();    //put columnname in ArrayList
                        foreach (DataColumn cColumn in dt.Columns)
                        {
                            ColumnNameInDT.Add(cColumn.ColumnName);
                        }
                        for (int j = 0; j < ColumnNameInDB.Count; j++)
                        {
                            if (ColumnNameInDT.Contains(ColumnNameInDB[j].ToString()))
                            {
                                //do nothing
                            }
                            else
                            {
                                dtColumnNameIsNotMatchTodb = -1;
                                IndexOfdtMissingColumn = j;
                                break;
                            }
                        }
                    }

                    #endregion

                    if (dtColumnNameIsNotMatchTodb == 0 && KeyColumnNameIsNotExistIndb == -1)
                    {
                        #region 构造执行bulk写入用datatable

                        int dtTableColumnCount = dtTableColumnName.Rows.Count;              //dtTableColumnName column count
                        DataTable bulkDataTable = new DataTable();                          //import datatable
                        DataColumn[] bulkDataColumn = new DataColumn[dtTableColumnCount];   //import datatable's DataColumn
                        System.Type columnType = typeof(System.String);                     //default column data type                 

                        for (int i = 0; i < dtTableColumnCount; i++)
                        {
                            switch (dtTableColumnName.Rows[i]["数据类型"].ToString())
                            {
                                default:
                                    columnType = typeof(System.String);
                                    break;
                                case "int":
                                    columnType = typeof(System.Int32);
                                    break;
                                case "text":
                                    columnType = typeof(System.String);
                                    break;
                                case "bigint":
                                    columnType = typeof(System.Int64);
                                    break;
                                case "binary":
                                    columnType = typeof(System.Byte[]);
                                    break;
                                case "bit":
                                    columnType = typeof(System.Boolean);
                                    break;
                                case "char":
                                    columnType = typeof(System.String);
                                    break;
                                case "datetime":
                                    columnType = typeof(System.DateTime);
                                    break;
                                case "decimal":
                                    columnType = typeof(System.Decimal);
                                    break;
                                case "float":
                                    columnType = typeof(System.Double);
                                    break;
                                case "image":
                                    columnType = typeof(System.Byte[]);
                                    break;
                                case "money":
                                    columnType = typeof(System.Decimal);
                                    break;
                                case "nchar":
                                    columnType = typeof(System.String);
                                    break;
                                case "ntext":
                                    columnType = typeof(System.String);
                                    break;
                                case "numeric":
                                    columnType = typeof(System.Decimal);
                                    break;
                                case "nvarchar":
                                    columnType = typeof(System.String);
                                    break;
                                case "real":
                                    columnType = typeof(System.Single);
                                    break;
                                case "smalldatetime":
                                    columnType = typeof(System.DateTime);
                                    break;
                                case "smallint":
                                    columnType = typeof(System.Int16);
                                    break;
                                case "smallmoney":
                                    columnType = typeof(System.Decimal);
                                    break;
                                case "timestamp":
                                    columnType = typeof(System.DateTime);
                                    break;
                                case "tinyint":
                                    columnType = typeof(System.Byte);
                                    break;
                                case "uniqueidentifier":
                                    columnType = typeof(System.Guid);
                                    break;
                                case "varbinary":
                                    columnType = typeof(System.Byte[]);
                                    break;
                                case "varchar":
                                    columnType = typeof(System.String);
                                    break;
                                case "Variant":
                                    columnType = typeof(System.Object);
                                    break;

                            }

                            bulkDataColumn[i] = new DataColumn(dtTableColumnName.Rows[i]["列名"].ToString(), columnType);
                            bulkDataTable.Columns.Add(bulkDataColumn[i]);
                        }

                        #endregion

                        #region dt copy to bulkDataTable

                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            DataRow tempRow = bulkDataTable.NewRow();

                            for (int j = 0; j < dtTableColumnCount; j++)
                            {
                                if (dt.Rows[i][j].ToString() == "" && dt.Columns[j].ColumnName != "位号")
                                { tempRow[j] = DBNull.Value; }
                                else
                                { tempRow[j] = dt.Rows[i][j]; }

                                //tempRow[j] = dt.Rows[i][j];
                            }

                            bulkDataTable.Rows.Add(tempRow);
                        }

                        #endregion

                        #region get original datatable

                        StringBuilder sbTableOriginal = new StringBuilder();
                        sbTableOriginal.Append("SELECT ");
                        sbTableOriginal.Append(bulkDataColumn[0].ColumnName);
                        for (int j = 1; j < bulkDataColumn.Length; j++)
                        {
                            sbTableOriginal.Append("," + bulkDataColumn[j].ColumnName);
                        }
                        sbTableOriginal.Append(" FROM ");
                        sbTableOriginal.Append(FullTableName);

                        if (sqlfilter != string.Empty)
                        {
                            sbTableOriginal.Append(" ");
                            sbTableOriginal.Append(sqlfilter);
                        }

                        SqlDataAdapter adapterTableOriginal = new SqlDataAdapter
                        {
                            SelectCommand = new SqlCommand(sbTableOriginal.ToString(), conn, sqltrans),
                            //adapterTableOriginal.SelectCommand.Parameters.Add(new SqlParameter("@tableName", SqlDbType.NVarChar, 220));
                            //adapterTableOriginal.SelectCommand.Parameters["@tableName"].Value = FullTableName;

                            //explain in msdn about here
                            //https://msdn.microsoft.com/zh-cn/library/system.data.missingschemaaction(v=vs.90).aspx
                            // MissingSchemaAction adds any missing schema to the DataTable, including identity columns
                            MissingSchemaAction = MissingSchemaAction.AddWithKey
                        };

                        // Fill the DataTable.
                        adapterTableOriginal.Fill(dtTableOriginal);

                        #endregion

                        #region put original datarow into ArrayList
                        //put original datarow into ArrayList

                        ArrayList ht = new ArrayList();
                        string hashkey = string.Empty;

                        foreach (DataRow myRow in dtTableOriginal.Rows)
                        {
                            hashkey = string.Empty;

                            foreach (string keyName in KeyColumnName)
                            {
                                hashkey += myRow[keyName].ToString() + ",";
                            }

                            //get rid of the last char ","
                            hashkey = hashkey.Substring(0, hashkey.Length - 1);

                            ht.Add(hashkey);
                        }

                        #endregion

                        #region ergodic bulkDataTable and judgement if data existed and handle it by DuplicateProcessMode

                        string XkeyValue = string.Empty;
                        DataTable dtDataTableAdd = bulkDataTable.Clone();          //can't remove row when bulkDataTable foreach is running
                        DataTable dtDataTableUpdate = dtTableOriginal.Clone();     //use for update   
                        int rowIgnoreCount = 0;
                        int rowDeleteCount = 0;

                        foreach (DataRow myRow in bulkDataTable.Rows)
                        {
                            XkeyValue = string.Empty;
                            foreach (string keyName in KeyColumnName)
                            {
                                XkeyValue += myRow[keyName].ToString() + ",";
                            }

                            //get rid of the last char ","
                            XkeyValue = XkeyValue.Substring(0, XkeyValue.Length - 1);

                            if (ht.Contains(XkeyValue))  //if exist
                            {
                                if (dupMode == DuplicateProcessMode.Ignore)
                                {
                                    rowIgnoreCount++;
                                }
                                else
                                {
                                    dtDataTableUpdate.Rows.Add(myRow.ItemArray);    //add this row to update table
                                }
                            }
                            else
                            {
                                dtDataTableAdd.Rows.Add(myRow.ItemArray);    //add this row to update table
                            }
                        }

                        #endregion

                        #region ergodic bulkDataTable and judgement if data deleted and handle it by DeleteProcessMode

                        //put delete datarow into ArrayList
                        ArrayList hts = new ArrayList();
                        string hashkeydelete = string.Empty;

                        foreach (DataRow myRow in bulkDataTable.Rows)
                        {
                            hashkeydelete = string.Empty;

                            foreach (string keyName in KeyColumnName)
                            {
                                hashkeydelete += myRow[keyName].ToString() + ",";
                            }

                            //get rid of the last char ","
                            hashkeydelete = hashkeydelete.Substring(0, hashkeydelete.Length - 1);

                            hts.Add(hashkeydelete);
                        }

                        string YkeyValue = string.Empty;
                        DataTable dtDataTableDelete = dtTableOriginal.Clone();  //use for update   

                        foreach (DataRow myRow in dtTableOriginal.Rows)
                        {
                            YkeyValue = string.Empty;
                            foreach (string keyName in KeyColumnName)
                            {
                                YkeyValue += myRow[keyName].ToString() + ",";
                            }

                            //get rid of the last char ","
                            YkeyValue = YkeyValue.Substring(0, YkeyValue.Length - 1);

                            if (hts.Contains(YkeyValue))  //if exist
                            {
                                //do nothing
                            }
                            else
                            {
                                dtDataTableDelete.Rows.Add(myRow.ItemArray);    //delete this row from update table
                            }
                        }

                        #endregion

                        #region handle dtTableUpdate

                        DataTable group = new DataTable();   //original dt
                        SqlDataAdapter adapterUpdate = new SqlDataAdapter
                        {
                            SelectCommand = new SqlCommand(sbTableOriginal.ToString(), conn, sqltrans)
                        };
                        //adapterUpdate.SelectCommand.Parameters.Add(new SqlParameter("@tableName", SqlDbType.UniqueIdentifier));
                        //adapterUpdate.SelectCommand.Parameters["@tableName"].Value = FullTableName;

                        //把key column从bulkDataColumn里拿出来
                        List<DataColumn> listValueColumn = bulkDataColumn.ToList();
                        List<DataColumn> listKeyColumn = new List<DataColumn>();     //存放Key

                        foreach (string keyName in KeyColumnName)
                        {
                            for (int i = 0; i < listValueColumn.Count; i++)
                            {
                                if (keyName == listValueColumn[i].ColumnName)
                                {
                                    listKeyColumn.Add(listValueColumn[i]);
                                    listValueColumn.RemoveAt(i);
                                    break;
                                }
                            }
                        }

                        DataColumn[] newlistValueColumn = listValueColumn.ToArray();
                        DataColumn[] newlistKeyColumn = listKeyColumn.ToArray();

                        //组装sbTableUpdate语句
                        StringBuilder sbTableUpdate = new StringBuilder();
                        sbTableUpdate.Append("UPDATE ");
                        sbTableUpdate.Append(FullTableName);
                        sbTableUpdate.Append(" SET ");
                        sbTableUpdate.Append(newlistValueColumn[0].ColumnName);
                        sbTableUpdate.Append("=@t0");
                        for (int j = 1; j < newlistValueColumn.Length; j++)
                        {
                            sbTableUpdate.Append("," + newlistValueColumn[j].ColumnName);
                            sbTableUpdate.Append("=@t");
                            sbTableUpdate.Append(j.ToString());
                        }
                        sbTableUpdate.Append(" WHERE ");
                        sbTableUpdate.Append(newlistKeyColumn[0].ColumnName);
                        sbTableUpdate.Append("=@c0");
                        for (int k = 1; k < newlistKeyColumn.Length; k++)
                        {
                            sbTableUpdate.Append(" AND ");
                            sbTableUpdate.Append(newlistKeyColumn[k].ColumnName);
                            sbTableUpdate.Append("=@c");
                            sbTableUpdate.Append(k.ToString());
                        }

                        adapterUpdate.UpdateCommand = new SqlCommand(sbTableUpdate.ToString(), conn, sqltrans);
                        //Add the parameter for the Update value. 
                        for (int p = 0; p < newlistValueColumn.Length; p++)
                        {
                            SqlDbType sdtp = GetDBType(newlistValueColumn[p].DataType);
                            adapterUpdate.UpdateCommand.Parameters.Add(new SqlParameter("@t" + p.ToString(), sdtp, newlistValueColumn[p].MaxLength, newlistValueColumn[p].ColumnName));
                        }

                        //Add the parameter for the Condition value. 
                        for (int c = 0; c < newlistKeyColumn.Length; c++)
                        {
                            SqlDbType sdtc = GetDBType(newlistKeyColumn[c].DataType);
                            adapterUpdate.UpdateCommand.Parameters.Add(new SqlParameter("@c" + c.ToString(), sdtc, newlistKeyColumn[c].MaxLength, newlistKeyColumn[c].ColumnName));
                        }
                        adapterUpdate.UpdateCommand.UpdatedRowSource = UpdateRowSource.Both;

                        //组装sbTableUpdate语句
                        StringBuilder sbTableDelete = new StringBuilder();
                        sbTableDelete.Append("DELETE FROM ");
                        sbTableDelete.Append(FullTableName);
                        sbTableDelete.Append(" WHERE ");
                        sbTableDelete.Append(newlistKeyColumn[0].ColumnName);
                        sbTableDelete.Append("=@c0");
                        for (int k = 1; k < newlistKeyColumn.Length; k++)
                        {
                            sbTableDelete.Append(" AND ");
                            sbTableDelete.Append(newlistKeyColumn[k].ColumnName);
                            sbTableDelete.Append("=@c");
                            sbTableDelete.Append(k.ToString());
                        }

                        adapterUpdate.DeleteCommand = new SqlCommand(sbTableDelete.ToString(), conn, sqltrans);
                        //Add the parameter for the Condition value. 
                        for (int c = 0; c < newlistKeyColumn.Length; c++)
                        {
                            SqlDbType sdtc = GetDBType(newlistKeyColumn[c].DataType);
                            adapterUpdate.DeleteCommand.Parameters.Add(new SqlParameter("@c" + c.ToString(), sdtc, newlistKeyColumn[c].MaxLength, newlistKeyColumn[c].ColumnName));
                        }
                        adapterUpdate.DeleteCommand.UpdatedRowSource = UpdateRowSource.Both;

                        // MissingSchemaAction adds any missing schema to 
                        // the DataTable, including identity columns
                        adapterUpdate.MissingSchemaAction = MissingSchemaAction.AddWithKey;

                        // Fill the DataTable.
                        adapterUpdate.Fill(group);

                        #endregion

                        #region handle group（update group）

                        int IsKeyColumnValueCompared = -1;   //default: -1
                        int IsValueColumnChanged = -1;       //default: -1

                        //group遍历改为从后向前    clementine   2016.10.19 
                        for (int i = group.Rows.Count - 1; i >= 0; i--)
                        {
                            for (int j = 0; j < dtDataTableUpdate.Rows.Count; j++)
                            {
                                /********************************************
                                 * keyColumnName
                                 ********************************************/
                                IsKeyColumnValueCompared = 1;       //1:yes , 2:no
                                for (int n = 0; n < KeyColumnName.Length; n++)
                                {
                                    if (group.Rows[i][KeyColumnName[n].ToString()].ToString() == dtDataTableUpdate.Rows[j][KeyColumnName[n].ToString()].ToString())
                                    { }
                                    else
                                    {
                                        IsKeyColumnValueCompared = 0;
                                        break;
                                    }
                                }

                                if (IsKeyColumnValueCompared == 1)
                                {
                                    IsValueColumnChanged = 1;       //1:yes , 2:no
                                                                    //update group value
                                    for (int k = 0; k < newlistValueColumn.Length; k++)
                                    {
                                        if (group.Rows[i][newlistValueColumn[k].ColumnName].ToString() == dtDataTableUpdate.Rows[j][newlistValueColumn[k].ColumnName].ToString())
                                        { }
                                        else
                                        {
                                            IsValueColumnChanged = 0;  //group.Rows[i]["X"] is not equal dtTableUpdate.Rows[i]["X"] 
                                            break;
                                        }
                                    }

                                    if (IsValueColumnChanged == 0)
                                    {
                                        for (int k = 0; k < newlistValueColumn.Length; k++)
                                        {
                                            group.Rows[i][newlistValueColumn[k].ColumnName] = dtDataTableUpdate.Rows[j][newlistValueColumn[k].ColumnName];
                                        }
                                    }
                                }
                                else
                                { }
                            }


                            for (int j = 0; j < dtDataTableDelete.Rows.Count; j++)
                            {
                                /********************************************
                                 * keyColumnName
                                 ********************************************/
                                IsKeyColumnValueCompared = 1;       //1:yes , 2:no
                                for (int n = 0; n < KeyColumnName.Length; n++)
                                {
                                    if (group.Rows[i][KeyColumnName[n].ToString()].ToString() == dtDataTableDelete.Rows[j][KeyColumnName[n].ToString()].ToString())
                                    { }
                                    else
                                    {
                                        IsKeyColumnValueCompared = 0;
                                        break;
                                    }
                                }

                                if (IsKeyColumnValueCompared == 1)
                                {
                                    //【旧代码】
                                    //group.Rows.Remove(group.Rows[i]);  //[i].Delete();

                                    //【问题】：删除批处理部分失效   clementine  2016.10.19 
                                    //【处理】此处应该使用Rows.Delete，不使用Rows.Remove
                                    group.Rows[i].Delete();
                                    rowDeleteCount++;
                                    break;
                                }
                                else
                                { }
                            }

                        }

                        DataTable dataChanges = group.GetChanges();
                        int dataChangesRowsCount = 0;
                        int dataInsertRowsCount = 0;

                        #endregion

                        #region bulk and commit

                        //int dataInsertRowsCount = 0;

                        if (dtDataTableAdd != null)
                        {
                            SqlBulkCopy sbc = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, sqltrans)
                            {
                                BulkCopyTimeout = bulkCopyTimeoutValue,
                                NotifyAfter = dtDataTableAdd.Rows.Count,
                                DestinationTableName = FullTableName
                            };
                            sbc.WriteToServer(dtDataTableAdd);
                            dataInsertRowsCount = dtDataTableAdd.Rows.Count;
                        }
                        else
                        {
                            dataInsertRowsCount = 0;
                        }

                        if (dataChanges != null)   //这里要判断一下，更新的行是否为空
                        {
                            adapterUpdate.Update(dataChanges);   //adapter.Update()参数dataChanges不能为空 
                            dataChangesRowsCount = dataChanges.Rows.Count;
                        }
                        else
                        {
                            dataChangesRowsCount = 0;
                        }

                        sqltrans.Commit();  //commit is here

                        #endregion

                        string relChangeOrIgnore = string.Empty;

                        if (dupMode == DuplicateProcessMode.Ignore)
                        {
                            relChangeOrIgnore = rowIgnoreCount.ToString();
                            string[] iRel = { "[1]", "执行成功，" + "插入:" + dataInsertRowsCount.ToString() + "，忽略:" + rowIgnoreCount.ToString() + "，删除:" + rowDeleteCount.ToString() };
                            return iRel;
                        }
                        else
                        {
                            relChangeOrIgnore = dataChangesRowsCount.ToString();
                            string[] iRel = { "[1]", "执行成功，" + "插入:" + dataInsertRowsCount.ToString() + "，更新:" + relChangeOrIgnore.ToString() + "，删除:" + rowDeleteCount.ToString() };
                            return iRel;
                        }

                    }
                    else if (KeyColumnNameIsNotExistIndb != -1)
                    {
                        string msg = "KeyColumnName的列:[" + KeyColumnName[KeyColumnNameIsNotExistIndb].ToString() + "]在数据库表[" + schemaName + "].[" + tableName + "]中不存在";
                        string[] iRel = { "[31]", msg };
                        return iRel;
                    }
                    else if (dtColumnNameIsNotMatchTodb == -1)  //dt column missing
                    {
                        string msg = "dt缺少列:[" + ColumnNameInDB[IndexOfdtMissingColumn].ToString() + "]";
                        string[] iRel = { "[32]", msg };
                        return iRel;
                    }
                    else if (dtColumnNameIsNotMatchTodb == -2)  //dt column Redundant
                    {
                        string msg = "dt有多余的列:[" + dt.Columns[IndexOfdtRedundantColumn].ColumnName + "]";
                        string[] iRel = { "[33]", msg };
                        return iRel;
                    }
                    else
                    {
                        //if come here you must see ghost
                        string[] iRel = { "[98]", "KeyColumnNameIsNotExistIndb或者dtColumnNameIsNotMatchTodb变量值异常" };
                        return iRel;
                    }

                }

            }
            //}
            //catch (Exception ex)
            //{
            //    if (sqltrans != null && sqltrans.Connection != null)
            //    {
            //        sqltrans.Rollback();
            //    }
            //    string[] iRel = { "[90]", ex.Message };
            //    return iRel;
            //}
            //finally
            //{
            //    if (conn.State != ConnectionState.Closed)
            //    {
            //        conn.Close(); //关闭连接
            //    }
            //}
        }

        /// <summary>
        /// 数据导入
        /// </summary>
        /// <param name="conn">连接对象</param>
        /// <param name="dt">数据集</param>
        /// <param name="schemaName">架构名</param>
        /// <param name="tableName">表名</param>
        /// <param name="DataKeyName">关键字名数组</param>（判断数据项的唯一性用）
        /// <param name="dup">对重复数据的处理方式</param>
        /// <returns>长度为2的字符数组<执行代码，提示><return value,message></returns>
        public string[] DataImport(SqlConnection conn, SqlTransaction sqltrans, DataTable dt, string schemaName, string tableName, string sqlfilter, string[] KeyColumnName, DuplicateProcessMode dupType)
        {
            /**********************************
             * 流程：
             * 1.解析dt的字段，组装成一个数组
             * 2.查询表tableName的数据集ds
             * 3.判断dt中有哪些项在ds中已经存在
             **********************************/
            #region declare

            DataTable dtTableColumnName = new DataTable();       //tableName column name
            string TableName = tableName.Trim();                 //tableName
            string FullTableName = schemaName.Trim() + "." + tableName.Trim();  //schemaName.tableName
            DataTable dtTableOriginal = new DataTable();         //data table original in DB

            #endregion

            //事务开始
            if (conn.State != ConnectionState.Open)
            {
                conn.Open(); //open conn
            }

            //SqlTransaction sqltrans = conn.BeginTransaction();   //Transaction Object

            try
            {
                if (dt.Rows.Count == 0)
                {
                    string[] iRel = { "[0]", "参数dt没有数据(0行)" };
                    return iRel;
                }
                else  //if dt has data
                {
                    #region 解析tableName列名和数据类型

                    StringBuilder sbTableColumnName = new StringBuilder();
                    sbTableColumnName.Append("SELECT COL.NAME AS 列名,TYP.NAME AS 数据类型,COL.MAX_LENGTH AS 占用字节数,");
                    sbTableColumnName.Append("COL.PRECISION AS 数字长度,COL.SCALE AS 小数位数,COL.IS_NULLABLE AS 是否允许非空,COL.IS_IDENTITY AS 是否自增,");
                    sbTableColumnName.Append("CASE WHEN EXISTS ");
                    sbTableColumnName.Append("( SELECT 1 FROM SYS.INDEXES IDX ");
                    sbTableColumnName.Append("JOIN SYS.INDEX_COLUMNS IDXCOL ON (IDX.OBJECT_ID = IDXCOL.OBJECT_ID) ");
                    sbTableColumnName.Append("WHERE IDX.OBJECT_ID = COL.OBJECT_ID ");
                    sbTableColumnName.Append("AND IDXCOL.INDEX_COLUMN_ID = COL.COLUMN_ID AND IDX.IS_PRIMARY_KEY = 1) ");
                    sbTableColumnName.Append("THEN 1 ELSE 0 END AS 是否是主键,ISNULL(PROP.[VALUE],'-') AS 说明 ");
                    sbTableColumnName.Append("FROM SYS.COLUMNS COL LEFT JOIN SYS.TYPES TYP ");
                    sbTableColumnName.Append("ON (COL.SYSTEM_TYPE_ID = TYP.SYSTEM_TYPE_ID) ");
                    sbTableColumnName.Append("LEFT JOIN SYS.EXTENDED_PROPERTIES PROP ");
                    sbTableColumnName.Append("ON (COL.OBJECT_ID = PROP.MAJOR_ID AND PROP.MINOR_ID = COL.COLUMN_ID) ");
                    sbTableColumnName.Append("WHERE COL.OBJECT_ID =(select OBJECT_ID from sys.tables t , sys.schemas s where t.schema_id = s.schema_id ");
                    sbTableColumnName.Append("and s.name = @schemaName and t.name = @tableName) ");
                    sbTableColumnName.Append("ORDER BY COL.COLUMN_ID");

                    SqlDataAdapter adapterTableColumnName = new SqlDataAdapter
                    {
                        SelectCommand = new SqlCommand(sbTableColumnName.ToString(), conn, sqltrans)
                    };
                    adapterTableColumnName.SelectCommand.Parameters.Add(new SqlParameter("@schemaName", SqlDbType.NVarChar, 220));
                    adapterTableColumnName.SelectCommand.Parameters["@schemaName"].Value = schemaName;
                    adapterTableColumnName.SelectCommand.Parameters.Add(new SqlParameter("@tableName", SqlDbType.NVarChar, 220));
                    adapterTableColumnName.SelectCommand.Parameters["@tableName"].Value = TableName;

                    //explain in msdn about here
                    //https://msdn.microsoft.com/zh-cn/library/system.data.missingschemaaction(v=vs.90).aspx
                    // MissingSchemaAction adds any missing schema to the DataTable, including identity columns
                    adapterTableColumnName.MissingSchemaAction = MissingSchemaAction.AddWithKey;

                    // Fill the DataTable.
                    adapterTableColumnName.Fill(dtTableColumnName);

                    #endregion

                    #region fix sysname by clementine 2016.6.19

                    for (int i = 0; i < dtTableColumnName.Rows.Count; i++)
                    {
                        if (dtTableColumnName.Rows[i]["数据类型"].ToString() == "sysname")
                        {
                            dtTableColumnName.Rows[i].Delete();
                        }
                    }

                    dtTableColumnName.AcceptChanges();

                    #endregion

                    #region check two things

                    ArrayList ColumnNameInDB = new ArrayList();    //put columnname in ArrayList
                    foreach (DataRow cRow in dtTableColumnName.Rows)
                    {
                        ColumnNameInDB.Add(cRow["列名"].ToString());
                    }

                    //one:if KeyColumnName in db's column
                    int KeyColumnNameIsNotExistIndb = -1;  //column index of key not exist in dtTableColumnName

                    for (int i = 0; i < KeyColumnName.Length; i++)
                    {
                        if (ColumnNameInDB.Contains(KeyColumnName[i].ToString()))
                        {
                            //do nothing
                        }
                        else
                        {
                            KeyColumnNameIsNotExistIndb = i;
                            break;
                        }
                    }

                    //two:if dtColumnName match to db's column
                    int dtColumnNameIsNotMatchTodb = 0; //-1:dt missing column  -2:dt redundant column
                    int IndexOfdtMissingColumn = -1;    //dt missing column index
                    int IndexOfdtRedundantColumn = -1;  //dt Redundant column index

                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        if (ColumnNameInDB.Contains(dt.Columns[i].ColumnName))
                        {
                            //do nothing
                        }
                        else
                        {
                            dtColumnNameIsNotMatchTodb = -2;
                            IndexOfdtRedundantColumn = i;
                            break;
                        }
                    }

                    if (dtColumnNameIsNotMatchTodb == 0)
                    {
                        ArrayList ColumnNameInDT = new ArrayList();    //put columnname in ArrayList
                        foreach (DataColumn cColumn in dt.Columns)
                        {
                            ColumnNameInDT.Add(cColumn.ColumnName);
                        }
                        for (int j = 0; j < ColumnNameInDB.Count; j++)
                        {
                            if (ColumnNameInDT.Contains(ColumnNameInDB[j].ToString()))
                            {
                                //do nothing
                            }
                            else
                            {
                                dtColumnNameIsNotMatchTodb = -1;
                                IndexOfdtMissingColumn = j;
                                break;
                            }
                        }
                    }

                    #endregion

                    if (dtColumnNameIsNotMatchTodb == 0 && KeyColumnNameIsNotExistIndb == -1)
                    {
                        #region 构造执行bulk写入用datatable

                        int dtTableColumnCount = dtTableColumnName.Rows.Count;              //dtTableColumnName column count
                        DataTable bulkDataTable = new DataTable();                          //import datatable
                        DataColumn[] bulkDataColumn = new DataColumn[dtTableColumnCount];   //import datatable's DataColumn
                        System.Type columnType = typeof(System.String);                     //default column data type                 

                        for (int i = 0; i < dtTableColumnCount; i++)
                        {
                            switch (dtTableColumnName.Rows[i]["数据类型"].ToString())
                            {
                                default:
                                    columnType = typeof(System.String);
                                    break;
                                case "int":
                                    columnType = typeof(System.Int32);
                                    break;
                                case "text":
                                    columnType = typeof(System.String);
                                    break;
                                case "bigint":
                                    columnType = typeof(System.Int64);
                                    break;
                                case "binary":
                                    columnType = typeof(System.Byte[]);
                                    break;
                                case "bit":
                                    columnType = typeof(System.Boolean);
                                    break;
                                case "char":
                                    columnType = typeof(System.String);
                                    break;
                                case "datetime":
                                    columnType = typeof(System.DateTime);
                                    break;
                                case "decimal":
                                    columnType = typeof(System.Decimal);
                                    break;
                                case "float":
                                    columnType = typeof(System.Double);
                                    break;
                                case "image":
                                    columnType = typeof(System.Byte[]);
                                    break;
                                case "money":
                                    columnType = typeof(System.Decimal);
                                    break;
                                case "nchar":
                                    columnType = typeof(System.String);
                                    break;
                                case "ntext":
                                    columnType = typeof(System.String);
                                    break;
                                case "numeric":
                                    columnType = typeof(System.Decimal);
                                    break;
                                case "nvarchar":
                                    columnType = typeof(System.String);
                                    break;
                                case "real":
                                    columnType = typeof(System.Single);
                                    break;
                                case "smalldatetime":
                                    columnType = typeof(System.DateTime);
                                    break;
                                case "smallint":
                                    columnType = typeof(System.Int16);
                                    break;
                                case "smallmoney":
                                    columnType = typeof(System.Decimal);
                                    break;
                                case "timestamp":
                                    columnType = typeof(System.DateTime);
                                    break;
                                case "tinyint":
                                    columnType = typeof(System.Byte);
                                    break;
                                case "uniqueidentifier":
                                    columnType = typeof(System.Guid);
                                    break;
                                case "varbinary":
                                    columnType = typeof(System.Byte[]);
                                    break;
                                case "varchar":
                                    columnType = typeof(System.String);
                                    break;
                                case "Variant":
                                    columnType = typeof(System.Object);
                                    break;

                            }

                            bulkDataColumn[i] = new DataColumn(dtTableColumnName.Rows[i]["列名"].ToString(), columnType);
                            bulkDataTable.Columns.Add(bulkDataColumn[i]);
                        }

                        #endregion

                        #region dt copy to bulkDataTable

                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            DataRow tempRow = bulkDataTable.NewRow();

                            for (int j = 0; j < dtTableColumnCount; j++)
                            {
                                if (dt.Rows[i][j].ToString() == "" && dt.Columns[j].ColumnName != "位号")
                                { tempRow[j] = DBNull.Value; }
                                else
                                { tempRow[j] = dt.Rows[i][j]; }
                            }

                            bulkDataTable.Rows.Add(tempRow);
                        }

                        #endregion

                        #region get original datatable

                        StringBuilder sbTableOriginal = new StringBuilder();
                        sbTableOriginal.Append("SELECT ");
                        sbTableOriginal.Append(bulkDataColumn[0].ColumnName);
                        for (int j = 1; j < bulkDataColumn.Length; j++)
                        {
                            sbTableOriginal.Append("," + bulkDataColumn[j].ColumnName);
                        }
                        sbTableOriginal.Append(" FROM ");
                        sbTableOriginal.Append(FullTableName);

                        if (sqlfilter != string.Empty)
                        {
                            sbTableOriginal.Append(" ");
                            sbTableOriginal.Append(sqlfilter);
                        }

                        SqlDataAdapter adapterTableOriginal = new SqlDataAdapter
                        {
                            SelectCommand = new SqlCommand(sbTableOriginal.ToString(), conn, sqltrans),
                            //adapterTableOriginal.SelectCommand.Parameters.Add(new SqlParameter("@tableName", SqlDbType.NVarChar, 220));
                            //adapterTableOriginal.SelectCommand.Parameters["@tableName"].Value = FullTableName;

                            //explain in msdn about here
                            //https://msdn.microsoft.com/zh-cn/library/system.data.missingschemaaction(v=vs.90).aspx
                            // MissingSchemaAction adds any missing schema to the DataTable, including identity columns
                            MissingSchemaAction = MissingSchemaAction.AddWithKey
                        };

                        // Fill the DataTable.
                        adapterTableOriginal.Fill(dtTableOriginal);

                        #endregion

                        #region judgement if data existed and handle it by DuplicateType

                        if (dupType == DuplicateProcessMode.Ignore)
                        {
                            #region put original datarow into ArrayList
                            //put original datarow into ArrayList

                            ArrayList ht = new ArrayList();
                            string hashkey = string.Empty;

                            foreach (DataRow myRow in dtTableOriginal.Rows)
                            {
                                hashkey = string.Empty;

                                foreach (string keyName in KeyColumnName)
                                {
                                    hashkey += myRow[keyName].ToString() + ",";
                                }

                                //get rid of the last char ","
                                hashkey = hashkey.Substring(0, hashkey.Length - 1);

                                ht.Add(hashkey);
                            }

                            #endregion

                            #region ergodic bulkDataTable and modify

                            string XkeyValue = string.Empty;
                            DataTable bulkDataTableCache = bulkDataTable.Clone();   //can't remove row when bulkDataTable foreach is running
                            int rowIgnoreCount = 0;

                            foreach (DataRow myRow in bulkDataTable.Rows)
                            {
                                XkeyValue = string.Empty;
                                foreach (string keyName in KeyColumnName)
                                {
                                    XkeyValue += myRow[keyName].ToString() + ",";
                                }

                                //get rid of the last char ","
                                XkeyValue = XkeyValue.Substring(0, XkeyValue.Length - 1);

                                if (ht.Contains(XkeyValue))  //if exist
                                {
                                    rowIgnoreCount++;
                                }
                                else
                                {
                                    bulkDataTableCache.Rows.Add(myRow.ItemArray);
                                }
                            }

                            #endregion

                            #region bulk and commit

                            int dataInsertRowsCount = 0;

                            if (bulkDataTableCache != null)
                            {
                                SqlBulkCopy sbc = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, sqltrans)
                                {
                                    BulkCopyTimeout = bulkCopyTimeoutValue,
                                    NotifyAfter = bulkDataTableCache.Rows.Count,
                                    DestinationTableName = FullTableName
                                };
                                sbc.WriteToServer(bulkDataTableCache);
                                dataInsertRowsCount = bulkDataTableCache.Rows.Count;
                            }
                            else
                            {
                                dataInsertRowsCount = 0;
                            }

                            //sqltrans.Commit();  //commit is here, out of the function  clementine  2017.8.23

                            string[] iRel = { "[1]", "执行成功，" + "插入:" + dataInsertRowsCount.ToString() + "，忽略:" + rowIgnoreCount.ToString() };
                            return iRel;

                            #endregion

                        }
                        else if (dupType == DuplicateProcessMode.Update)  //if update
                        {
                            #region put original datarow into ArrayList
                            //put original datarow into ArrayList

                            ArrayList ht = new ArrayList();
                            string hashkey = string.Empty;
                            DataTable dtTableUpdate = dtTableOriginal.Clone();   //use for update   

                            foreach (DataRow myRow in dtTableOriginal.Rows)
                            {
                                hashkey = string.Empty;

                                foreach (string keyName in KeyColumnName)
                                {
                                    hashkey += myRow[keyName].ToString() + ",";
                                }

                                //get rid of the last char ","
                                hashkey = hashkey.Substring(0, hashkey.Length - 1);

                                ht.Add(hashkey);
                            }

                            #endregion

                            #region ergodic bulkDataTable and modify

                            string XkeyValue = string.Empty;
                            DataTable bulkDataTableCache = bulkDataTable.Clone();   //can't remove row when bulkDataTable foreach is running
                                                                                    //关于【集合已修改；可能无法执行枚举操作。】的问题解决,留存待退休以后研究
                                                                                    //http://www.cnblogs.com/luckly-hf/archive/2012/08/08/2628025.html

                            foreach (DataRow myRow in bulkDataTable.Rows)
                            {
                                XkeyValue = string.Empty;
                                foreach (string keyName in KeyColumnName)
                                {
                                    XkeyValue += myRow[keyName].ToString() + ",";
                                }

                                //get rid of the last char ","
                                XkeyValue = XkeyValue.Substring(0, XkeyValue.Length - 1);

                                if (ht.Contains(XkeyValue))  //if exist
                                {
                                    dtTableUpdate.Rows.Add(myRow.ItemArray);    //add this row to update table
                                                                                //bulkDataTable.Rows.Remove(myRow);         //I want to remove this row from bulk table,but can't remove row when bulkDataTable foreach is running
                                }
                                else
                                {
                                    //add this row into bulkDataTableCache
                                    bulkDataTableCache.Rows.Add(myRow.ItemArray);
                                }
                            }

                            #endregion

                            #region handle dtTableUpdate

                            DataTable group = new DataTable();   //original dt
                            SqlDataAdapter adapterUpdate = new SqlDataAdapter
                            {
                                SelectCommand = new SqlCommand(sbTableOriginal.ToString(), conn, sqltrans)
                            };
                            //adapterUpdate.SelectCommand.Parameters.Add(new SqlParameter("@tableName", SqlDbType.UniqueIdentifier));
                            //adapterUpdate.SelectCommand.Parameters["@tableName"].Value = FullTableName;

                            //把key column从bulkDataColumn里拿出来
                            List<DataColumn> listValueColumn = bulkDataColumn.ToList();
                            List<DataColumn> listKeyColumn = new List<DataColumn>();     //存放Key

                            foreach (string keyName in KeyColumnName)
                            {
                                for (int i = 0; i < listValueColumn.Count; i++)
                                {
                                    if (keyName == listValueColumn[i].ColumnName)
                                    {
                                        listKeyColumn.Add(listValueColumn[i]);
                                        listValueColumn.RemoveAt(i);
                                        break;
                                    }
                                }
                            }

                            DataColumn[] newlistValueColumn = listValueColumn.ToArray();
                            DataColumn[] newlistKeyColumn = listKeyColumn.ToArray();

                            //组装sbTableUpdate语句
                            StringBuilder sbTableUpdate = new StringBuilder();
                            sbTableUpdate.Append("UPDATE ");
                            sbTableUpdate.Append(FullTableName);
                            sbTableUpdate.Append(" SET ");
                            sbTableUpdate.Append(newlistValueColumn[0].ColumnName);
                            sbTableUpdate.Append("=@t0");
                            for (int j = 1; j < newlistValueColumn.Length; j++)
                            {
                                sbTableUpdate.Append("," + newlistValueColumn[j].ColumnName);
                                sbTableUpdate.Append("=@t");
                                sbTableUpdate.Append(j.ToString());
                            }
                            sbTableUpdate.Append(" WHERE ");
                            sbTableUpdate.Append(newlistKeyColumn[0].ColumnName);
                            sbTableUpdate.Append("=@c0");
                            for (int k = 1; k < newlistKeyColumn.Length; k++)
                            {
                                sbTableUpdate.Append(" AND ");
                                sbTableUpdate.Append(newlistKeyColumn[k].ColumnName);
                                sbTableUpdate.Append("=@c");
                                sbTableUpdate.Append(k.ToString());
                            }

                            adapterUpdate.UpdateCommand = new SqlCommand(sbTableUpdate.ToString(), conn, sqltrans);
                            //Add the parameter for the Update value. 
                            for (int p = 0; p < newlistValueColumn.Length; p++)
                            {
                                SqlDbType sdtp = GetDBType(newlistValueColumn[p].DataType);
                                adapterUpdate.UpdateCommand.Parameters.Add(new SqlParameter("@t" + p.ToString(), sdtp, newlistValueColumn[p].MaxLength, newlistValueColumn[p].ColumnName));
                            }

                            //Add the parameter for the Condition value. 
                            for (int c = 0; c < newlistKeyColumn.Length; c++)
                            {
                                SqlDbType sdtc = GetDBType(newlistKeyColumn[c].DataType);
                                adapterUpdate.UpdateCommand.Parameters.Add(new SqlParameter("@c" + c.ToString(), sdtc, newlistKeyColumn[c].MaxLength, newlistKeyColumn[c].ColumnName));
                            }
                            adapterUpdate.UpdateCommand.UpdatedRowSource = UpdateRowSource.Both;

                            // MissingSchemaAction adds any missing schema to 
                            // the DataTable, including identity columns
                            adapterUpdate.MissingSchemaAction = MissingSchemaAction.AddWithKey;

                            // Fill the DataTable.
                            adapterUpdate.Fill(group);

                            #endregion

                            #region handle group（update group）

                            int IsKeyColumnValueCompared = -1;   //default: -1
                            int IsValueColumnChanged = -1;       //default: -1

                            for (int i = 0; i < group.Rows.Count; i++)
                            {
                                for (int j = 0; j < dtTableUpdate.Rows.Count; j++)
                                {
                                    /********************************************
                                     * keyColumnName
                                     ********************************************/
                                    IsKeyColumnValueCompared = 1;       //1:yes , 2:no
                                    for (int n = 0; n < KeyColumnName.Length; n++)
                                    {
                                        if (group.Rows[i][KeyColumnName[n].ToString()].ToString() == dtTableUpdate.Rows[j][KeyColumnName[n].ToString()].ToString())
                                        { }
                                        else
                                        {
                                            IsKeyColumnValueCompared = 0;
                                            break;
                                        }
                                    }

                                    if (IsKeyColumnValueCompared == 1)
                                    {
                                        IsValueColumnChanged = 1;       //1:yes , 2:no
                                                                        //update group value
                                        for (int k = 0; k < newlistValueColumn.Length; k++)
                                        {
                                            if (group.Rows[i][newlistValueColumn[k].ColumnName].ToString() == dtTableUpdate.Rows[j][newlistValueColumn[k].ColumnName].ToString())
                                            { }
                                            else
                                            {
                                                IsValueColumnChanged = 0;  //group.Rows[i]["X"] is not equal dtTableUpdate.Rows[i]["X"] 
                                                break;
                                            }
                                        }

                                        if (IsValueColumnChanged == 0)
                                        {
                                            for (int k = 0; k < newlistValueColumn.Length; k++)
                                            {
                                                group.Rows[i][newlistValueColumn[k].ColumnName] = dtTableUpdate.Rows[j][newlistValueColumn[k].ColumnName];
                                            }
                                        }
                                    }
                                    else
                                    { }
                                }

                            }

                            DataTable dataChanges = group.GetChanges();
                            int dataChangesRowsCount = 0;
                            int dataInsertRowsCount = 0;

                            // Add the event handler.
                            //adapter.RowUpdated += new SqlRowUpdatedEventHandler(OnRowUpdated);

                            if (dataChanges != null)   //这里要判断一下，更新的行是否为空
                            {
                                adapterUpdate.Update(dataChanges);   //adapter.Update()参数dataChanges不能为空 
                                dataChangesRowsCount = dataChanges.Rows.Count;
                            }
                            else
                            {
                                dataChangesRowsCount = 0;
                            }

                            #endregion

                            #region bulk and commit

                            if (bulkDataTableCache != null)
                            {
                                SqlBulkCopy sbc = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, sqltrans)
                                {
                                    BulkCopyTimeout = bulkCopyTimeoutValue,
                                    NotifyAfter = bulkDataTableCache.Rows.Count,
                                    DestinationTableName = FullTableName
                                };
                                sbc.WriteToServer(bulkDataTableCache);
                                dataInsertRowsCount = bulkDataTableCache.Rows.Count;
                            }
                            else
                            {
                                dataInsertRowsCount = 0;
                            }

                            string[] iRel = { "[1]", "执行成功，" + "插入:" + dataInsertRowsCount.ToString() + "，更新:" + dataChangesRowsCount.ToString() };
                            //sqltrans.Commit();  //commit in the end
                            return iRel;

                            #endregion
                        }
                        else
                        {
                            //if come here you must see ghost
                            string[] iRel = { "[99]", "如果你见到这句话，那肯定是见鬼了" };
                            return iRel;
                        }

                        #endregion

                    }
                    else if (KeyColumnNameIsNotExistIndb != -1)
                    {
                        string msg = "KeyColumnName的列:[" + KeyColumnName[KeyColumnNameIsNotExistIndb].ToString() + "]在数据库表[" + schemaName + "].[" + tableName + "]中不存在";
                        string[] iRel = { "[31]", msg };
                        return iRel;
                    }
                    else if (dtColumnNameIsNotMatchTodb == -1)  //dt column missing
                    {
                        string msg = "dt缺少列:[" + ColumnNameInDB[IndexOfdtMissingColumn].ToString() + "]";
                        string[] iRel = { "[32]", msg };
                        return iRel;
                    }
                    else if (dtColumnNameIsNotMatchTodb == -2)  //dt column Redundant
                    {
                        string msg = "dt有多余的列:[" + dt.Columns[IndexOfdtRedundantColumn].ColumnName + "]";
                        string[] iRel = { "[33]", msg };
                        return iRel;
                    }
                    else
                    {
                        //if come here you must see ghost
                        string[] iRel = { "[98]", "KeyColumnNameIsNotExistIndb或者dtColumnNameIsNotMatchTodb变量值异常" };
                        return iRel;
                    }

                }


            }
            catch (Exception ex)
            {
                string[] iRel = { "[90]", ex.Message };
                return iRel;
            }
            finally
            {
                //if (conn.State != ConnectionState.Closed)
                //{
                //    conn.Close(); //关闭连接
                //}
            }
        }

        /// <summary>
        /// 数据更新
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="dt"></param>
        /// <param name="schemaName"></param>
        /// <param name="tableName"></param>
        /// <param name="sqlfilter"></param>
        /// <param name="KeyColumnName"></param>
        /// <param name="dupMode">已经存在项处理方式</param>
        /// <param name="delMode">删除项处理方式</param>
        /// <returns>长度为2的字符数组<执行代码，提示><return value,message></returns>
        public string[] DataUpdate(SqlConnection conn, SqlTransaction sqltrans, DataTable dt, string schemaName, string tableName, string sqlfilter, string[] KeyColumnName, DuplicateProcessMode dupMode)
        {
            /**********************************
             * 流程：
             * 1.解析dt的字段，组装成一个数组
             * 2.查询表tableName的数据集ds
             * 3.判断dt中有哪些项在ds中已经存在
             **********************************/
            #region declare

            DataTable dtTableColumnName = new DataTable();       //tableName column name
            string TableName = tableName.Trim();                 //tableName
            string FullTableName = schemaName.Trim() + "." + tableName.Trim();  //schemaName.tableName
            DataTable dtTableOriginal = new DataTable();         //data table original in DB

            #endregion

            //事务开始
            if (conn.State != ConnectionState.Open)
            {
                conn.Open(); //open conn
            }

            //SqlTransaction sqltrans = conn.BeginTransaction();   //Transaction Object

            try
            {

                if (dt.Rows.Count == 0)
                {
                    string[] iRel = { "[0]", "参数dt没有数据(0行)" };
                    return iRel;
                }
                else  //if dt has data
                {
                    #region 解析tableName列名和数据类型

                    StringBuilder sbTableColumnName = new StringBuilder();
                    sbTableColumnName.Append("SELECT COL.NAME AS 列名,TYP.NAME AS 数据类型,COL.MAX_LENGTH AS 占用字节数,");
                    sbTableColumnName.Append("COL.PRECISION AS 数字长度,COL.SCALE AS 小数位数,COL.IS_NULLABLE AS 是否允许非空,COL.IS_IDENTITY AS 是否自增,");
                    sbTableColumnName.Append("CASE WHEN EXISTS ");
                    sbTableColumnName.Append("( SELECT 1 FROM SYS.INDEXES IDX ");
                    sbTableColumnName.Append("JOIN SYS.INDEX_COLUMNS IDXCOL ON (IDX.OBJECT_ID = IDXCOL.OBJECT_ID) ");
                    sbTableColumnName.Append("WHERE IDX.OBJECT_ID = COL.OBJECT_ID ");
                    sbTableColumnName.Append("AND IDXCOL.INDEX_COLUMN_ID = COL.COLUMN_ID AND IDX.IS_PRIMARY_KEY = 1) ");
                    sbTableColumnName.Append("THEN 1 ELSE 0 END AS 是否是主键,ISNULL(PROP.[VALUE],'-') AS 说明 ");
                    sbTableColumnName.Append("FROM SYS.COLUMNS COL LEFT JOIN SYS.TYPES TYP ");
                    sbTableColumnName.Append("ON (COL.SYSTEM_TYPE_ID = TYP.SYSTEM_TYPE_ID) ");
                    sbTableColumnName.Append("LEFT JOIN SYS.EXTENDED_PROPERTIES PROP ");
                    sbTableColumnName.Append("ON (COL.OBJECT_ID = PROP.MAJOR_ID AND PROP.MINOR_ID = COL.COLUMN_ID) ");
                    sbTableColumnName.Append("WHERE COL.OBJECT_ID =(select OBJECT_ID from sys.tables t , sys.schemas s where t.schema_id = s.schema_id ");
                    sbTableColumnName.Append("and s.name = @schemaName and t.name = @tableName) ");
                    sbTableColumnName.Append("ORDER BY COL.COLUMN_ID");

                    SqlDataAdapter adapterTableColumnName = new SqlDataAdapter
                    {
                        SelectCommand = new SqlCommand(sbTableColumnName.ToString(), conn, sqltrans)
                    };
                    adapterTableColumnName.SelectCommand.Parameters.Add(new SqlParameter("@schemaName", SqlDbType.NVarChar, 220));
                    adapterTableColumnName.SelectCommand.Parameters["@schemaName"].Value = schemaName;
                    adapterTableColumnName.SelectCommand.Parameters.Add(new SqlParameter("@tableName", SqlDbType.NVarChar, 220));
                    adapterTableColumnName.SelectCommand.Parameters["@tableName"].Value = TableName;

                    //explain in msdn about here
                    //https://msdn.microsoft.com/zh-cn/library/system.data.missingschemaaction(v=vs.90).aspx
                    // MissingSchemaAction adds any missing schema to the DataTable, including identity columns
                    adapterTableColumnName.MissingSchemaAction = MissingSchemaAction.AddWithKey;

                    // Fill the DataTable.
                    adapterTableColumnName.Fill(dtTableColumnName);

                    #endregion

                    #region fix sysname

                    for (int i = 0; i < dtTableColumnName.Rows.Count; i++)
                    {
                        if (dtTableColumnName.Rows[i]["数据类型"].ToString() == "sysname")
                        {
                            dtTableColumnName.Rows[i].Delete();
                        }
                    }

                    dtTableColumnName.AcceptChanges();

                    #endregion

                    #region check two things

                    ArrayList ColumnNameInDB = new ArrayList();    //put columnname in ArrayList
                    foreach (DataRow cRow in dtTableColumnName.Rows)
                    {
                        ColumnNameInDB.Add(cRow["列名"].ToString());
                    }

                    //one:if KeyColumnName in db's column
                    int KeyColumnNameIsNotExistIndb = -1;  //column index of key not exist in dtTableColumnName

                    for (int i = 0; i < KeyColumnName.Length; i++)
                    {
                        if (ColumnNameInDB.Contains(KeyColumnName[i].ToString()))
                        {
                            //do nothing
                        }
                        else
                        {
                            KeyColumnNameIsNotExistIndb = i;
                            break;
                        }
                    }

                    //two:if dtColumnName match to db's column
                    int dtColumnNameIsNotMatchTodb = 0; //-1:dt missing column  -2:dt redundant column
                    int IndexOfdtMissingColumn = -1;    //dt missing column index
                    int IndexOfdtRedundantColumn = -1;  //dt Redundant column index

                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        if (ColumnNameInDB.Contains(dt.Columns[i].ColumnName))
                        {
                            //do nothing
                        }
                        else
                        {
                            dtColumnNameIsNotMatchTodb = -2;
                            IndexOfdtRedundantColumn = i;
                            break;
                        }
                    }

                    if (dtColumnNameIsNotMatchTodb == 0)
                    {
                        ArrayList ColumnNameInDT = new ArrayList();    //put columnname in ArrayList
                        foreach (DataColumn cColumn in dt.Columns)
                        {
                            ColumnNameInDT.Add(cColumn.ColumnName);
                        }
                        for (int j = 0; j < ColumnNameInDB.Count; j++)
                        {
                            if (ColumnNameInDT.Contains(ColumnNameInDB[j].ToString()))
                            {
                                //do nothing
                            }
                            else
                            {
                                dtColumnNameIsNotMatchTodb = -1;
                                IndexOfdtMissingColumn = j;
                                break;
                            }
                        }
                    }

                    #endregion

                    if (dtColumnNameIsNotMatchTodb == 0 && KeyColumnNameIsNotExistIndb == -1)
                    {
                        #region 构造执行bulk写入用datatable

                        int dtTableColumnCount = dtTableColumnName.Rows.Count;              //dtTableColumnName column count
                        DataTable bulkDataTable = new DataTable();                          //import datatable
                        DataColumn[] bulkDataColumn = new DataColumn[dtTableColumnCount];   //import datatable's DataColumn
                        System.Type columnType = typeof(System.String);                     //default column data type                 

                        for (int i = 0; i < dtTableColumnCount; i++)
                        {
                            switch (dtTableColumnName.Rows[i]["数据类型"].ToString())
                            {
                                default:
                                    columnType = typeof(System.String);
                                    break;
                                case "int":
                                    columnType = typeof(System.Int32);
                                    break;
                                case "text":
                                    columnType = typeof(System.String);
                                    break;
                                case "bigint":
                                    columnType = typeof(System.Int64);
                                    break;
                                case "binary":
                                    columnType = typeof(System.Byte[]);
                                    break;
                                case "bit":
                                    columnType = typeof(System.Boolean);
                                    break;
                                case "char":
                                    columnType = typeof(System.String);
                                    break;
                                case "datetime":
                                    columnType = typeof(System.DateTime);
                                    break;
                                case "decimal":
                                    columnType = typeof(System.Decimal);
                                    break;
                                case "float":
                                    columnType = typeof(System.Double);
                                    break;
                                case "image":
                                    columnType = typeof(System.Byte[]);
                                    break;
                                case "money":
                                    columnType = typeof(System.Decimal);
                                    break;
                                case "nchar":
                                    columnType = typeof(System.String);
                                    break;
                                case "ntext":
                                    columnType = typeof(System.String);
                                    break;
                                case "numeric":
                                    columnType = typeof(System.Decimal);
                                    break;
                                case "nvarchar":
                                    columnType = typeof(System.String);
                                    break;
                                case "real":
                                    columnType = typeof(System.Single);
                                    break;
                                case "smalldatetime":
                                    columnType = typeof(System.DateTime);
                                    break;
                                case "smallint":
                                    columnType = typeof(System.Int16);
                                    break;
                                case "smallmoney":
                                    columnType = typeof(System.Decimal);
                                    break;
                                case "timestamp":
                                    columnType = typeof(System.DateTime);
                                    break;
                                case "tinyint":
                                    columnType = typeof(System.Byte);
                                    break;
                                case "uniqueidentifier":
                                    columnType = typeof(System.Guid);
                                    break;
                                case "varbinary":
                                    columnType = typeof(System.Byte[]);
                                    break;
                                case "varchar":
                                    columnType = typeof(System.String);
                                    break;
                                case "Variant":
                                    columnType = typeof(System.Object);
                                    break;

                            }

                            bulkDataColumn[i] = new DataColumn(dtTableColumnName.Rows[i]["列名"].ToString(), columnType);
                            bulkDataTable.Columns.Add(bulkDataColumn[i]);
                        }

                        #endregion

                        #region dt copy to bulkDataTable

                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            DataRow tempRow = bulkDataTable.NewRow();

                            for (int j = 0; j < dtTableColumnCount; j++)
                            {
                                if (dt.Rows[i][j].ToString() == "" && dt.Columns[j].ColumnName != "位号")
                                { tempRow[j] = DBNull.Value; }
                                else
                                { tempRow[j] = dt.Rows[i][j]; }
                            }

                            bulkDataTable.Rows.Add(tempRow);
                        }

                        #endregion

                        #region get original datatable

                        StringBuilder sbTableOriginal = new StringBuilder();
                        sbTableOriginal.Append("SELECT ");
                        sbTableOriginal.Append(bulkDataColumn[0].ColumnName);
                        for (int j = 1; j < bulkDataColumn.Length; j++)
                        {
                            sbTableOriginal.Append("," + bulkDataColumn[j].ColumnName);
                        }
                        sbTableOriginal.Append(" FROM ");
                        sbTableOriginal.Append(FullTableName);

                        if (sqlfilter != string.Empty)
                        {
                            sbTableOriginal.Append(" ");
                            sbTableOriginal.Append(sqlfilter);
                        }

                        SqlDataAdapter adapterTableOriginal = new SqlDataAdapter
                        {
                            SelectCommand = new SqlCommand(sbTableOriginal.ToString(), conn, sqltrans),
                            //adapterTableOriginal.SelectCommand.Parameters.Add(new SqlParameter("@tableName", SqlDbType.NVarChar, 220));
                            //adapterTableOriginal.SelectCommand.Parameters["@tableName"].Value = FullTableName;

                            //explain in msdn about here
                            //https://msdn.microsoft.com/zh-cn/library/system.data.missingschemaaction(v=vs.90).aspx
                            // MissingSchemaAction adds any missing schema to the DataTable, including identity columns
                            MissingSchemaAction = MissingSchemaAction.AddWithKey
                        };

                        // Fill the DataTable.
                        adapterTableOriginal.Fill(dtTableOriginal);

                        #endregion

                        #region put original datarow into ArrayList
                        //put original datarow into ArrayList

                        ArrayList ht = new ArrayList();
                        string hashkey = string.Empty;

                        foreach (DataRow myRow in dtTableOriginal.Rows)
                        {
                            hashkey = string.Empty;

                            foreach (string keyName in KeyColumnName)
                            {
                                hashkey += myRow[keyName].ToString() + ",";
                            }

                            //get rid of the last char ","
                            hashkey = hashkey.Substring(0, hashkey.Length - 1);

                            ht.Add(hashkey);
                        }

                        #endregion

                        #region ergodic bulkDataTable and judgement if data existed and handle it by DuplicateProcessMode

                        string XkeyValue = string.Empty;
                        DataTable dtDataTableAdd = bulkDataTable.Clone();          //can't remove row when bulkDataTable foreach is running
                        DataTable dtDataTableUpdate = dtTableOriginal.Clone();     //use for update   
                        int rowIgnoreCount = 0;
                        int rowDeleteCount = 0;

                        foreach (DataRow myRow in bulkDataTable.Rows)
                        {
                            XkeyValue = string.Empty;
                            foreach (string keyName in KeyColumnName)
                            {
                                XkeyValue += myRow[keyName].ToString() + ",";
                            }

                            //get rid of the last char ","
                            XkeyValue = XkeyValue.Substring(0, XkeyValue.Length - 1);

                            if (ht.Contains(XkeyValue))  //if exist
                            {
                                if (dupMode == DuplicateProcessMode.Ignore)
                                {
                                    rowIgnoreCount++;
                                }
                                else
                                {
                                    dtDataTableUpdate.Rows.Add(myRow.ItemArray);    //add this row to update table
                                }
                            }
                            else
                            {
                                dtDataTableAdd.Rows.Add(myRow.ItemArray);    //add this row to update table
                            }
                        }

                        #endregion

                        #region ergodic bulkDataTable and judgement if data deleted and handle it by DeleteProcessMode

                        //put delete datarow into ArrayList
                        ArrayList hts = new ArrayList();
                        string hashkeydelete = string.Empty;

                        foreach (DataRow myRow in bulkDataTable.Rows)
                        {
                            hashkeydelete = string.Empty;

                            foreach (string keyName in KeyColumnName)
                            {
                                hashkeydelete += myRow[keyName].ToString() + ",";
                            }

                            //get rid of the last char ","
                            hashkeydelete = hashkeydelete.Substring(0, hashkeydelete.Length - 1);

                            hts.Add(hashkeydelete);
                        }

                        string YkeyValue = string.Empty;
                        DataTable dtDataTableDelete = dtTableOriginal.Clone();  //use for update   

                        foreach (DataRow myRow in dtTableOriginal.Rows)
                        {
                            YkeyValue = string.Empty;
                            foreach (string keyName in KeyColumnName)
                            {
                                YkeyValue += myRow[keyName].ToString() + ",";
                            }

                            //get rid of the last char ","
                            YkeyValue = YkeyValue.Substring(0, YkeyValue.Length - 1);

                            if (hts.Contains(YkeyValue))  //if exist
                            {
                                //do nothing
                            }
                            else
                            {
                                dtDataTableDelete.Rows.Add(myRow.ItemArray);    //delete this row from update table
                            }
                        }

                        #endregion

                        #region handle dtTableUpdate

                        DataTable group = new DataTable();   //original dt
                        SqlDataAdapter adapterUpdate = new SqlDataAdapter
                        {
                            SelectCommand = new SqlCommand(sbTableOriginal.ToString(), conn, sqltrans)
                        };
                        //adapterUpdate.SelectCommand.Parameters.Add(new SqlParameter("@tableName", SqlDbType.UniqueIdentifier));
                        //adapterUpdate.SelectCommand.Parameters["@tableName"].Value = FullTableName;

                        //把key column从bulkDataColumn里拿出来
                        List<DataColumn> listValueColumn = bulkDataColumn.ToList();
                        List<DataColumn> listKeyColumn = new List<DataColumn>();     //存放Key

                        foreach (string keyName in KeyColumnName)
                        {
                            for (int i = 0; i < listValueColumn.Count; i++)
                            {
                                if (keyName == listValueColumn[i].ColumnName)
                                {
                                    listKeyColumn.Add(listValueColumn[i]);
                                    listValueColumn.RemoveAt(i);
                                    break;
                                }
                            }
                        }

                        DataColumn[] newlistValueColumn = listValueColumn.ToArray();
                        DataColumn[] newlistKeyColumn = listKeyColumn.ToArray();

                        //组装sbTableUpdate语句
                        StringBuilder sbTableUpdate = new StringBuilder();
                        sbTableUpdate.Append("UPDATE ");
                        sbTableUpdate.Append(FullTableName);
                        sbTableUpdate.Append(" SET ");
                        sbTableUpdate.Append(newlistValueColumn[0].ColumnName);
                        sbTableUpdate.Append("=@t0");
                        for (int j = 1; j < newlistValueColumn.Length; j++)
                        {
                            sbTableUpdate.Append("," + newlistValueColumn[j].ColumnName);
                            sbTableUpdate.Append("=@t");
                            sbTableUpdate.Append(j.ToString());
                        }
                        sbTableUpdate.Append(" WHERE ");
                        sbTableUpdate.Append(newlistKeyColumn[0].ColumnName);
                        sbTableUpdate.Append("=@c0");
                        for (int k = 1; k < newlistKeyColumn.Length; k++)
                        {
                            sbTableUpdate.Append(" AND ");
                            sbTableUpdate.Append(newlistKeyColumn[k].ColumnName);
                            sbTableUpdate.Append("=@c");
                            sbTableUpdate.Append(k.ToString());
                        }

                        adapterUpdate.UpdateCommand = new SqlCommand(sbTableUpdate.ToString(), conn, sqltrans);
                        //Add the parameter for the Update value. 
                        for (int p = 0; p < newlistValueColumn.Length; p++)
                        {
                            SqlDbType sdtp = GetDBType(newlistValueColumn[p].DataType);
                            adapterUpdate.UpdateCommand.Parameters.Add(new SqlParameter("@t" + p.ToString(), sdtp, newlistValueColumn[p].MaxLength, newlistValueColumn[p].ColumnName));
                        }

                        //Add the parameter for the Condition value. 
                        for (int c = 0; c < newlistKeyColumn.Length; c++)
                        {
                            SqlDbType sdtc = GetDBType(newlistKeyColumn[c].DataType);
                            adapterUpdate.UpdateCommand.Parameters.Add(new SqlParameter("@c" + c.ToString(), sdtc, newlistKeyColumn[c].MaxLength, newlistKeyColumn[c].ColumnName));
                        }
                        adapterUpdate.UpdateCommand.UpdatedRowSource = UpdateRowSource.Both;

                        //组装sbTableUpdate语句
                        StringBuilder sbTableDelete = new StringBuilder();
                        sbTableDelete.Append("DELETE FROM ");
                        sbTableDelete.Append(FullTableName);
                        sbTableDelete.Append(" WHERE ");
                        sbTableDelete.Append(newlistKeyColumn[0].ColumnName);
                        sbTableDelete.Append("=@c0");
                        for (int k = 1; k < newlistKeyColumn.Length; k++)
                        {
                            sbTableDelete.Append(" AND ");
                            sbTableDelete.Append(newlistKeyColumn[k].ColumnName);
                            sbTableDelete.Append("=@c");
                            sbTableDelete.Append(k.ToString());
                        }

                        adapterUpdate.DeleteCommand = new SqlCommand(sbTableDelete.ToString(), conn, sqltrans);
                        //Add the parameter for the Condition value. 
                        for (int c = 0; c < newlistKeyColumn.Length; c++)
                        {
                            SqlDbType sdtc = GetDBType(newlistKeyColumn[c].DataType);
                            adapterUpdate.DeleteCommand.Parameters.Add(new SqlParameter("@c" + c.ToString(), sdtc, newlistKeyColumn[c].MaxLength, newlistKeyColumn[c].ColumnName));
                        }
                        adapterUpdate.DeleteCommand.UpdatedRowSource = UpdateRowSource.Both;

                        // MissingSchemaAction adds any missing schema to 
                        // the DataTable, including identity columns
                        adapterUpdate.MissingSchemaAction = MissingSchemaAction.AddWithKey;

                        // Fill the DataTable.
                        adapterUpdate.Fill(group);

                        #endregion

                        #region handle group（update group）

                        int IsKeyColumnValueCompared = -1;   //default: -1
                        int IsValueColumnChanged = -1;       //default: -1

                        //group遍历改为从后向前    clementine   2016.10.19 
                        for (int i = group.Rows.Count - 1; i >= 0; i--)
                        {
                            for (int j = 0; j < dtDataTableUpdate.Rows.Count; j++)
                            {
                                /********************************************
                                 * keyColumnName
                                 ********************************************/
                                IsKeyColumnValueCompared = 1;       //1:yes , 2:no
                                for (int n = 0; n < KeyColumnName.Length; n++)
                                {
                                    if (group.Rows[i][KeyColumnName[n].ToString()].ToString() == dtDataTableUpdate.Rows[j][KeyColumnName[n].ToString()].ToString())
                                    { }
                                    else
                                    {
                                        IsKeyColumnValueCompared = 0;
                                        break;
                                    }
                                }

                                if (IsKeyColumnValueCompared == 1)
                                {
                                    IsValueColumnChanged = 1;       //1:yes , 2:no
                                                                    //update group value
                                    for (int k = 0; k < newlistValueColumn.Length; k++)
                                    {
                                        if (group.Rows[i][newlistValueColumn[k].ColumnName].ToString() == dtDataTableUpdate.Rows[j][newlistValueColumn[k].ColumnName].ToString())
                                        { }
                                        else
                                        {
                                            IsValueColumnChanged = 0;  //group.Rows[i]["X"] is not equal dtTableUpdate.Rows[i]["X"] 
                                            break;
                                        }
                                    }

                                    if (IsValueColumnChanged == 0)
                                    {
                                        for (int k = 0; k < newlistValueColumn.Length; k++)
                                        {
                                            group.Rows[i][newlistValueColumn[k].ColumnName] = dtDataTableUpdate.Rows[j][newlistValueColumn[k].ColumnName];
                                        }
                                    }
                                }
                                else
                                {

                                }
                            }


                            for (int j = 0; j < dtDataTableDelete.Rows.Count; j++)
                            {
                                /********************************************
                                 * keyColumnName
                                 ********************************************/
                                IsKeyColumnValueCompared = 1;       //1:yes , 2:no
                                for (int n = 0; n < KeyColumnName.Length; n++)
                                {
                                    if (group.Rows[i][KeyColumnName[n].ToString()].ToString() == dtDataTableDelete.Rows[j][KeyColumnName[n].ToString()].ToString())
                                    { }
                                    else
                                    {
                                        IsKeyColumnValueCompared = 0;
                                        break;
                                    }
                                }

                                if (IsKeyColumnValueCompared == 1)
                                {
                                    //【旧代码】
                                    //group.Rows.Remove(group.Rows[i]);  //[i].Delete();

                                    //【问题】：删除批处理部分失效   clementine  2016.10.19 
                                    //【处理】此处应该使用Rows.Delete，不使用Rows.Remove
                                    group.Rows[i].Delete();
                                    rowDeleteCount++;
                                    break;
                                }
                                else
                                { }
                            }

                        }

                        DataTable dataChanges = group.GetChanges();
                        int dataChangesRowsCount = 0;
                        int dataInsertRowsCount = 0;

                        #endregion

                        #region bulk and commit

                        //int dataInsertRowsCount = 0;

                        if (dtDataTableAdd != null)
                        {
                            SqlBulkCopy sbc = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, sqltrans)
                            {
                                BulkCopyTimeout = bulkCopyTimeoutValue,
                                NotifyAfter = dtDataTableAdd.Rows.Count,
                                DestinationTableName = FullTableName
                            };
                            sbc.WriteToServer(dtDataTableAdd);
                            dataInsertRowsCount = dtDataTableAdd.Rows.Count;
                        }
                        else
                        {
                            dataInsertRowsCount = 0;
                        }

                        if (dataChanges != null)   //这里要判断一下，更新的行是否为空
                        {
                            adapterUpdate.Update(dataChanges);   //adapter.Update()参数dataChanges不能为空 
                            dataChangesRowsCount = dataChanges.Rows.Count;
                        }
                        else
                        {
                            dataChangesRowsCount = 0;
                        }

                        //sqltrans.Commit();  //commit is here

                        #endregion

                        string relChangeOrIgnore = string.Empty;

                        if (dupMode == DuplicateProcessMode.Ignore)
                        {
                            relChangeOrIgnore = rowIgnoreCount.ToString();
                            string[] iRel = { "[1]", "执行成功，" + "插入:" + dataInsertRowsCount.ToString() + "，忽略:" + rowIgnoreCount.ToString() + "，删除:" + rowDeleteCount.ToString() };
                            return iRel;
                        }
                        else
                        {
                            relChangeOrIgnore = dataChangesRowsCount.ToString();
                            string[] iRel = { "[1]", "执行成功，" + "插入:" + dataInsertRowsCount.ToString() + "，更新:" + relChangeOrIgnore.ToString() + "，删除:" + rowDeleteCount.ToString() };
                            return iRel;
                        }

                    }
                    else if (KeyColumnNameIsNotExistIndb != -1)
                    {
                        string msg = "KeyColumnName的列:[" + KeyColumnName[KeyColumnNameIsNotExistIndb].ToString() + "]在数据库表[" + schemaName + "].[" + tableName + "]中不存在";
                        string[] iRel = { "[31]", msg };
                        return iRel;
                    }
                    else if (dtColumnNameIsNotMatchTodb == -1)  //dt column missing
                    {
                        string msg = "dt缺少列:[" + ColumnNameInDB[IndexOfdtMissingColumn].ToString() + "]";
                        string[] iRel = { "[32]", msg };
                        return iRel;
                    }
                    else if (dtColumnNameIsNotMatchTodb == -2)  //dt column Redundant
                    {
                        string msg = "dt有多余的列:[" + dt.Columns[IndexOfdtRedundantColumn].ColumnName + "]";
                        string[] iRel = { "[33]", msg };
                        return iRel;
                    }
                    else
                    {
                        //if come here you must see ghost
                        string[] iRel = { "[98]", "KeyColumnNameIsNotExistIndb或者dtColumnNameIsNotMatchTodb变量值异常" };
                        return iRel;
                    }

                }


            }
            catch (Exception ex)
            {
                string[] iRel = { "[90]", ex.Message };
                return iRel;
            }
            finally
            {
                //    if (conn.State != ConnectionState.Closed)
                //    {
                //        conn.Close(); //关闭连接
                //    }
            }
        }


        /// <summary>
        /// BulkToDB方法
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="dtname"></param>
        /// <param name="conn"></param>
        /// <param name="sqltran"></param>
        private static void BulkToDB(DataTable dt, string dtname, SqlConnection conn, SqlTransaction sqltran)
        {
            SqlBulkCopy sbc = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, sqltran)
            {
                BulkCopyTimeout = 5000,
                //sbc.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnRowsCopied);
                NotifyAfter = dt.Rows.Count,
                DestinationTableName = dtname
            };
            sbc.WriteToServer(dt);
        }

        /// <summary>
        /// change System.Type to SqlDbType
        /// </summary>
        /// <param name="theType"></param>
        /// <returns></returns>
        private SqlDbType GetDBType(System.Type theType)
        {
            SqlParameter p1;
            System.ComponentModel.TypeConverter tc;
            p1 = new SqlParameter();
            tc = System.ComponentModel.TypeDescriptor.GetConverter(p1.DbType);
            if (tc.CanConvertFrom(theType))
            {
                p1.DbType = (DbType)tc.ConvertFrom(theType.Name);
            }
            else
            {        //Try brute force    
                try
                {
                    //原代码
                    //p1.DbType = (DbType)tc.ConvertFrom(theType.Name);

                    //2016.9.26 BUG 二进制类型转换失败临时解决办法
                    if (theType.Name == "Byte[]")
                    {
                        p1.DbType = DbType.Binary;
                    }
                    else
                    {
                        p1.DbType = (DbType)tc.ConvertFrom(theType.Name);
                    }
                }
                catch
                {
                    //Do Nothing    
                }
            }
            return p1.SqlDbType;
        }
    }

    /// <summary>
    /// 重复数据项处理方式
    /// </summary>
    public enum DuplicateProcessMode  //显示指定枚举的底层数据类型
    {
        Update,   //更新重复数据
        Ignore,   //忽略重复数据
    };

}
